SQLSTATE 22002 occurs when a NULL value is fetched from the database but no indicator variable was specified to capture its null status. This commonly happens in embedded SQL (ECPG) or applications using cursor variables without proper null handling.
PostgreSQL error code 22002 indicates a NULL value was returned from a database operation (FETCH, SELECT, or CALL statement), but the host variable receiving the value has not provided an indicator variable. Indicator variables are used to signal when a returned value is NULL, allowing applications to distinguish between NULL and empty/default values. This is particularly relevant in embedded SQL (ECPG in C), cursor operations, and stored procedures where type-safe null handling is critical. The indicator variable is a data mechanism defined in the SQL standard to handle the inherent NULL value problem: how do you represent NULL in a host language like C that may not natively support NULL values in scalar types? When a FETCH or SELECT INTO operation encounters a NULL value in a column but the host variable lacks an indicator variable, PostgreSQL raises SQLSTATE 22002 to prevent silent data loss or undefined behavior.
Locate the FETCH, embedded SELECT, or CALL statement that triggered the error. Check which INTO clause or parameter list position caused the issue. The error message may indicate the relative position of the problematic host variable.
// This will fail with 22002:
int user_id;
char username[50];
EXEC SQL FETCH user_cursor INTO :user_id, :username;
// If username column contains NULL, error occursAdd indicator variables (typically short integers) alongside your host variables. Modify your ECPG code from:
EXEC SQL FETCH cursor_name INTO :host_variable;To:
EXEC SQL FETCH cursor_name INTO :host_variable :indicator_variable;The indicator variable will be set to -1 if the value is NULL, 0 if it contains data, or a positive value for string truncation warnings.
After fetching, verify the indicator variable before using the host variable:
short username_indicator;
char username[50];
EXEC SQL FETCH user_cursor INTO :username :username_indicator;
if (username_indicator == -1) {
// Value is NULL, handle appropriately
printf("Username is NULL\n");
strcpy(username, "unknown");
} else if (username_indicator > 0) {
// String was truncated
printf("Warning: username truncated, original length: %d\n", username_indicator);
} else {
// Use username safely
printf("Username: %s\n", username);
}When selecting multiple columns, use an array of indicator variables:
int user_id;
char username[50];
char email[100];
short indicators[3]; // One for each column
EXEC SQL FETCH cursor INTO :user_id :indicators[0], :username :indicators[1], :email :indicators[2];
for (int i = 0; i < 3; i++) {
if (indicators[i] == -1) {
printf("Column %d is NULL\n", i);
}
}If using stored procedures, ensure output variables are initialized or use proper null handling:
CREATE OR REPLACE FUNCTION get_user_profile(
user_id_param INTEGER,
OUT user_name TEXT,
OUT email TEXT,
OUT phone TEXT
) AS $$
BEGIN
SELECT name, email_address, phone_number
INTO user_name, email, phone
FROM users
WHERE id = user_id_param;
-- If no rows found, all OUT variables are NULL
-- Client must use indicators to detect this
END;
$$ LANGUAGE plpgsql;For regular SQL queries, use COALESCE to provide default values for NULLs, eliminating the need for indicator variables:
SELECT
user_id,
COALESCE(username, 'unknown') as username,
COALESCE(email, '[email protected]') as email
FROM users
WHERE active = true;
-- In ECPG, this eliminates NULL columns so indicators aren't needed
EXEC SQL SELECT user_id, COALESCE(username, 'unknown'), COALESCE(email, 'no-email')
INTO :id, :name, :email FROM users;Create a test case that explicitly includes NULL values to ensure your indicator variable handling works correctly:
// Test cursor with nullable data
EXEC SQL DECLARE test_cursor CURSOR FOR
SELECT id, name, bio FROM users ORDER BY id;
int user_id;
char name[100];
char bio[500];
short name_ind, bio_ind;
EXEC SQL FETCH test_cursor INTO :user_id, :name :name_ind, :bio :bio_ind;
// Verify indicators are properly set
assert(name_ind == 0 || name_ind == -1);
assert(bio_ind == 0 || bio_ind == -1);SQLSTATE 22002 is primarily relevant for embedded SQL (ECPG), cursor operations, and database drivers that strictly enforce SQL standard compliance. Most modern ORMs and database libraries (Prisma, SQLAlchemy, JDBC) abstract away indicator variables and handle NULL automatically. However, you may encounter this error when using ECPG for high-performance C/C++ database access, writing low-level database drivers, or using older database tools that strictly follow SQL standards. The SQL standard defines indicator variables (ISO/IEC 9075) as a way to handle nullable columns in embedded SQL contexts where type systems may not natively support NULL. PostgreSQL's native PL/pgSQL doesn't use indicator variablesโNULL is handled natively. The error typically surfaces when using PostgreSQL through embedded SQL interfaces (libpq with ECPG) or when ODBC/JDBC drivers haven't properly mapped null values. Valid indicator values: -1 (NULL), 0 (non-NULL data), positive integers (string truncation warning with original length).
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL