PostgreSQL Invalid Indicator Parameter Value (SQLSTATE 22010)

When working with embedded SQL (ECPG), PostgreSQL raises ERROR: invalid indicator parameter value with SQLSTATE 22010 and condition name invalid_indicator_parameter_value. This error means an indicator variable used in a host variable binding contains a value that is not within the range PostgreSQL recognizes as valid for that purpose.

What This Error Means

SQLSTATE 22010 belongs to error class 22 — Data Exception. This entire class covers errors related to invalid data values during SQL operations, including things like numeric overflow, invalid datetime formats, and string truncation. 22010 specifically targets indicator variables, which are a concept unique to embedded SQL programming.

In ECPG (the embedded SQL preprocessor for C programs that ships with PostgreSQL), indicator variables are integer host variables that accompany a data host variable. The indicator variable signals special states: a negative value typically means the fetched column was NULL, a positive value means the data was truncated, and zero means the value was retrieved successfully. When PostgreSQL receives an indicator variable set to a value it cannot interpret — particularly a value that is out of the permissible range for the expected indicator semantics — it raises SQLSTATE 22010.

This error surfaces at the C/embedded SQL layer rather than in plain SQL sessions. You will not encounter it when running queries through psql, a JDBC driver, or most ORMs. It is specific to programs compiled with ecpg that use indicator variables in FETCH, EXECUTE, or SELECT INTO statements.

Common Causes

1. Out-of-range indicator value passed to an output binding. An indicator variable for an output host variable is set to a value that does not map to any valid state (NULL indicator, truncation indicator, or normal). For example, passing a large positive integer that exceeds what the ECPG runtime treats as a truncation signal.

2. Incorrect indicator variable type. The C variable declared as an indicator is not a compatible integer type (int, short, long), causing the runtime to read a garbage or out-of-range value.

3. Uninitialized indicator variable used as input. When using indicator variables for INSERT or UPDATE input bindings, an uninitialized variable may hold an arbitrary value. If that value is neither -1 (NULL) nor 0 (non-NULL), the server may reject it.

4. Mismatch between declared indicator and host variable. The indicator variable is associated with the wrong host variable due to a positional error in the ECPG declaration or the USING clause.

How to Fix invalid_indicator_parameter_value

1. Initialize indicator variables explicitly before use. Always set your indicator variable to a known state before executing a statement:

   int val_ind = 0;   /* 0 = value is present, not NULL */
   /* or */
   int val_ind = -1;  /* -1 = treat as NULL on input */
   

2. Declare indicator variables as the correct integer type. ECPG requires indicator variables to be short, int, or long. Using char, float, or other types leads to undefined behavior and can produce out-of-range values at runtime:

   /* Correct */
   int    salary_ind;
   short  name_ind;
   
   /* Wrong — do not use these as indicators */
   char   bad_ind;
   double also_bad_ind;
   

3. Verify indicator variable associations in FETCH and SELECT INTO. In ECPG, each host variable that can receive a NULL must be followed by its own indicator variable. Confirm the positional order matches between declarations and the SQL statement:

   EXEC SQL FETCH cur INTO :salary :salary_ind, :name :name_ind;
   

   A shifted association means one variable receives the other's indicator, easily producing an invalid value.

4. Check for truncation handling logic. After a FETCH, a positive indicator value signals truncation. If your code sets or copies an indicator variable based on application logic and mistakenly writes an arbitrary positive number, validate that you only write 0, -1, or read the indicator passively after a fetch.

5. Use EXEC SQL WHENEVER SQLERROR to capture the exact statement. Wrap sections of your ECPG program to identify which statement triggers the error:

   EXEC SQL WHENEVER SQLERROR DO handle_error();
   

Additional Information

• SQLSTATE class 22 (Data Exception) contains many related codes. Nearby sibling conditions include 22002 (null_value_no_indicator_parameter) — raised when a fetched column is NULL but no indicator variable was provided — and 22001 (string_data_right_truncation).
• 22010 is defined in the SQL standard and implemented in PostgreSQL's ECPG runtime. It does not apply to server-side PL/pgSQL, SQL functions, or any client driver that does not use embedded SQL indicator semantics.
• JDBC, libpq, and psycopg2 do not use indicator variables in the ECPG sense; NULL handling in those drivers is done through the driver's own null-checking API. This error will not appear in applications using those interfaces.
• The error is non-fatal in the sense that the transaction is not automatically aborted by PostgreSQL itself, but the ECPG runtime will typically raise it as an exception that terminates the statement. Whether the surrounding transaction is rolled back depends on your application's error handling.
• This error is rare in modern development because ECPG usage has declined significantly. It is most commonly encountered in legacy C applications or database migration projects involving older codebases.

Frequently Asked Questions

Why does this error never appear when I run queries in psql or through my ORM? SQLSTATE 22010 is specific to the ECPG embedded SQL preprocessor. Indicator variables are an embedded SQL concept; psql, JDBC, psycopg2, and ORMs handle NULL through their own APIs and never send indicator variable values to PostgreSQL in a way that triggers this code path.

What is the valid range for an indicator variable value? The SQL standard specifies that a negative indicator value (commonly -1) signals a NULL input or output. A value of 0 means the data is present and complete. A positive value after a fetch signals that the retrieved string was truncated to fit the host variable buffer. Any other value — particularly large out-of-range integers from uninitialized memory — is invalid and will trigger 22010.

Can I reproduce this error in plain SQL for testing? No. There is no standard SQL syntax in PostgreSQL's interactive interface that exercises indicator variable handling. To reproduce or test around this error you need a compiled ECPG program.

How is 22010 different from 22002 (null_value_no_indicator_parameter)? 22002 fires when PostgreSQL fetches a NULL column value but the calling program provided no indicator variable to receive that NULL signal — the program simply has no way to express "this was NULL." 22010 fires when an indicator variable was provided but its current value falls outside the valid range.