PostgreSQL Null Value No Indicator Parameter (SQLSTATE 22002)

PostgreSQL raises SQLSTATE 22002 (null_value_no_indicator_parameter) when an embedded SQL (ECPG) query returns a NULL value into a host variable, but no indicator variable was declared to receive it. The error message typically appears as:

ERROR:  null value returned for host variable without an indicator

This condition is part of PostgreSQL's Class 22 — Data Exception family of errors.

What This Error Means

In PostgreSQL's embedded SQL preprocessor (ECPG), host variables are C variables that receive output from SQL queries. When a column value in the result is NULL, SQL has no way to represent that through a plain C type — a char array, int, or double cannot natively represent the absence of a value. The standard SQL solution is the indicator variable: a companion short int that is set to -1 when the corresponding host variable contains a NULL result.

SQLSTATE 22002 is raised at runtime when PostgreSQL's ECPG runtime library tries to assign a NULL result to a host variable, but no indicator variable was provided in the INTO clause of the SELECT or FETCH statement. Because there is nowhere to signal the NULL condition, the runtime considers this a programming error and raises the exception rather than silently assigning an arbitrary or zero value to the host variable.

The error occurs at the point of data retrieval — the query itself executes successfully, and the connection remains open and usable. The current transaction is not automatically rolled back unless the application's error handling does so explicitly.

Common Causes

1. Missing indicator variable in an ECPG INTO clause. A host variable is declared and used in SELECT ... INTO :myvar, but no :myvar_ind indicator variable accompanies it, and the retrieved column contains NULL.

2. Nullable columns not accounted for in embedded SQL code. The schema allows NULLs in a column (no NOT NULL constraint), but the ECPG code was written assuming the column always has a value. A later data change introduces NULLs and begins triggering the error.

3. Outer join or aggregation producing NULLs. A query that previously returned non-NULL values is modified to use a LEFT JOIN or an aggregate like MAX()/MIN() over an empty set. The result can now be NULL even though the original host variable binding did not account for it.

4. FETCH on a cursor with nullable columns. Cursors declared over queries with nullable output columns and fetched into host variables without indicator variables will raise this error on the first NULL row encountered.

How to Fix null_value_no_indicator_parameter

1. Declare and bind an indicator variable for every nullable host variable.

   For each host variable that may receive a NULL, declare a companion short int indicator and reference it in the INTO clause:

   EXEC SQL BEGIN DECLARE SECTION;
       char customer_name[100];
       short int customer_name_ind;   /* indicator variable */
       int customer_id;
   EXEC SQL END DECLARE SECTION;
   
   EXEC SQL SELECT name INTO :customer_name :customer_name_ind
            FROM customers WHERE id = :customer_id;
   
   if (customer_name_ind == -1) {
       /* column was NULL */
       printf("customer name is NULL\n");
   } else {
       printf("customer name: %s\n", customer_name);
   }
   

   When the column value is NULL, the ECPG runtime sets customer_name_ind to -1. When non-NULL, it is set to 0 (or the original string length if truncation occurred).

2. Add indicator variables to cursor FETCH statements.

   The same pattern applies when fetching from a cursor:

   EXEC SQL FETCH cursor1 INTO :col_val :col_val_ind;
   

3. Eliminate NULLs at the SQL level using COALESCE. If a NULL result is genuinely unexpected or undesirable, rewrite the query to substitute a default value so that the host variable never receives NULL:

   SELECT COALESCE(name, '') AS name FROM customers WHERE id = :customer_id;
   

   This avoids the indicator variable entirely, but hides potential data quality issues.

4. Add NOT NULL constraints on columns that should never be NULL. If the NULL originates from a data integrity issue rather than intentional design, enforce the constraint at the schema level to prevent future occurrences.

Additional Information

• SQLSTATE 22002 is defined in the SQL standard and is not specific to any PostgreSQL version. ECPG has supported indicator variables since its earliest releases.
• Related SQLSTATE codes in Class 22 (Data Exception) include:
  • 22001 — string_data_right_truncation (host variable too short for the value)
  • 22003 — numeric_value_out_of_range
  • 22007 — invalid_datetime_format
  • 22012 — division_by_zero
• This error is specific to embedded SQL (ECPG) programs. It does not occur when using libpq (the C API), JDBC, psycopg2, or other driver-level interfaces — those represent NULLs natively within their own result types.
• The ECPG preprocessor (ecpg) does not warn at compile time when indicator variables are absent for potentially nullable columns; the check is a runtime condition only.
• In ECPG, indicator variables can also detect truncation: a positive value in the indicator means the data was truncated to fit the host variable's declared size.

Frequently Asked Questions

Why does this error only happen at runtime and not at compile time?

The ECPG preprocessor has no knowledge of the database schema when it compiles the C source. It cannot know which columns are nullable, so it cannot warn that an indicator variable is missing. The check is deferred to runtime when the actual query result is available.

Does this error affect the database connection or transaction?

No. SQLSTATE 22002 is a client-side error raised by the ECPG runtime library after the query has successfully returned results. The connection remains open, and any active transaction is still intact. Your application's WHENEVER SQLERROR handler or explicit sqlca.sqlcode check will receive the error, but no automatic rollback occurs.

I don't use ECPG — can I encounter SQLSTATE 22002?

It is extremely unlikely. SQLSTATE 22002 is specific to the embedded SQL interface and the concept of host variables with indicator parameters. If you are using libpq, JDBC, psycopg2, asyncpg, or any ORM, NULL values are handled transparently by the driver and this error will not be raised.

Can I suppress this error and just get the NULL silently?

The correct approach is always to use an indicator variable. However, if you are absolutely certain a column cannot be NULL in practice, using COALESCE in the query to substitute a default is an acceptable workaround. Suppressing the ECPG error without handling it risks your program operating on undefined or zero-initialized host variable data.