PostgreSQL 22010 Error: Causes and Solutions Complete Guide

PostgreSQL Error 22010: Invalid Indicator Parameter Value

PostgreSQL error code 22010 — "invalid indicator parameter value" — occurs when an indicator parameter passed alongside a host variable contains a value outside the permitted range defined by the SQL standard. This error is most commonly encountered in Embedded SQL (ECPG) programs, ODBC-based applications, or any client interface that uses indicator variables to communicate NULL status or data truncation information to the database server.

---

Top 3 Causes

1. Wrong Indicator Value in ECPG Programs

The SQL standard only allows three categories of indicator values: -1 (represents NULL), 0 (normal non-NULL value), or a positive integer (original length when data was truncated). Passing any other value — such as -99 or arbitrary negative numbers — will immediately trigger error 22010.

-- ECPG example: WRONG vs CORRECT indicator usage

-- ❌ WRONG: arbitrary negative value causes 22010
emp_name_ind = -99;  /* Invalid! */

EXEC SQL INSERT INTO employees (name)
    VALUES (:emp_name :emp_name_ind);

-- ✅ CORRECT: use -1 for NULL, 0 for normal values
emp_name_ind = -1;   /* NULL indicator per SQL standard */

EXEC SQL INSERT INTO employees (name)
    VALUES (:emp_name :emp_name_ind);

-- ✅ CORRECT: non-NULL value
emp_name_ind = 0;
strcpy(emp_name, "John Doe");

EXEC SQL INSERT INTO employees (name)
    VALUES (:emp_name :emp_name_ind);

2. Incorrect ODBC Parameter Binding

When using ODBC to connect to PostgreSQL, the StrLen_or_IndPtr argument in SQLBindParameter must be set to SQL_NULL_DATA for NULLs or SQL_NTS for null-terminated strings. Passing a non-standard integer value in this field can result in error 22010 being returned from the server.

-- Table used for ODBC binding test
CREATE TABLE orders (
    order_id    SERIAL PRIMARY KEY,
    customer    VARCHAR(100) NOT NULL,
    notes       TEXT,          -- nullable column
    total       NUMERIC(10,2)
);

-- After fixing ODBC binding, verify NULL handling is correct:
SELECT
    order_id,
    customer,
    CASE
        WHEN notes IS NULL THEN '[NULL - indicator was SQL_NULL_DATA]'
        ELSE notes
    END AS notes_status,
    total
FROM orders
ORDER BY order_id DESC
LIMIT 5;

3. Uninitialized or Reused Indicator Variables

In C-based ECPG programs, forgetting to initialize indicator variables before use — or reusing them across loop iterations without resetting — can leave garbage values that PostgreSQL rejects as invalid indicators. This is especially common in multi-threaded applications where indicator variables are shared across threads.

-- ECPG loop: always reinitialize indicators each iteration
EXEC SQL BEGIN DECLARE SECTION;
    int   emp_id;
    short emp_id_ind;
    char  emp_name[100];
    short emp_name_ind;
    float salary;
    short salary_ind;
EXEC SQL END DECLARE SECTION;

/*
  C code — reinitialize every iteration:

  for (int i = 0; i < count; i++) {
      emp_id_ind   = 0;
      emp_name_ind = 0;
      salary_ind   = (records[i].salary_defined) ? 0 : -1;

      emp_id = records[i].id;
      strcpy(emp_name, records[i].name);
      salary = records[i].salary;

      EXEC SQL INSERT INTO employees (id, name, salary)
          VALUES (:emp_id   :emp_id_ind,
                  :emp_name :emp_name_ind,
                  :salary   :salary_ind);
  }
*/

-- Validate results after the loop
SELECT id, name, salary,
       (salary IS NULL) AS salary_is_null
FROM employees
ORDER BY id DESC
LIMIT 10;

---

Quick Fix Solutions

1. Audit all indicator variables — Search your codebase for any indicator variable assignments and confirm they are strictly -1, 0, or a positive integer.
2. Upgrade ODBC/JDBC drivers — Outdated drivers sometimes pass malformed indicator values. Always use the latest stable PostgreSQL-certified driver.
3. Add a server-side guard function to catch bad indicator values early during development:

CREATE OR REPLACE FUNCTION check_indicator(p_ind INTEGER)
RETURNS BOOLEAN
LANGUAGE plpgsql AS $$
BEGIN
    IF p_ind < -1 THEN
        RAISE EXCEPTION 'Invalid indicator: %. Use -1 (NULL), 0 (value), or positive (truncation).',
            p_ind USING ERRCODE = '22010';
    END IF;
    RETURN TRUE;
END;
$$;

-- Test it
SELECT check_indicator(0);    -- OK
SELECT check_indicator(-1);   -- OK (NULL)
SELECT check_indicator(-5);   -- Raises 22010

---

Prevention Tips

• Wrap indicator logic in helper macros or functions. Never set indicator variables inline throughout your code. Centralizing this logic reduces human error and makes audits straightforward.
• Add NULL-handling test cases to your CI/CD pipeline. Include automated tests that explicitly insert and retrieve NULL values via ECPG or ODBC to catch 22010 regressions before they reach production.

---

Related Errors

Code	Name	Relation
22000	data exception	Parent category of 22010
22002	null value, no indicator parameter	Opposite problem: missing indicator
22001	string data right truncation	Related to positive indicator (truncation length)
22003	numeric value out of range	Similar parameter-binding context

---

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.