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 an illegal or out-of-range value. Indicator parameters are special variables used primarily in ECPG (Embedded SQL in C) and ODBC environments to signal NULL values or data status, and they must conform to strictly defined value ranges. When a value outside the accepted range (e.g., any negative number other than -1) is provided, PostgreSQL rejects it with this error.

Top 3 Causes

1. Uninitialized or Incorrect Indicator Variable in ECPG

The most common cause is using an uninitialized or arbitrarily assigned negative value for an indicator variable in embedded C SQL code. Only -1 (NULL) and >= 0 (valid data) are acceptable.

-- Correct ECPG usage (conceptual SQL equivalent)

-- WRONG: arbitrary negative value (triggers 22010)
-- name_indicator = -99;  /* DO NOT USE */

-- CORRECT: using -1 to represent NULL
-- name_indicator = -1;
INSERT INTO employees (id, name)
VALUES (1, NULL);  -- equivalent NULL insertion

-- CORRECT: using 0 for a valid value
-- name_indicator = 0;
INSERT INTO employees (id, name)
VALUES (1, 'John Doe');

2. Incorrect ODBC StrLen_or_IndPtr Value in SQLBindParameter

When using ODBC, passing a non-standard value to StrLen_or_IndPtr in SQLBindParameter sends an invalid indicator to PostgreSQL. Only standardized ODBC constants are acceptable.

-- Verify parameter handling on the PostgreSQL server side
-- Enable verbose logging in postgresql.conf:
-- log_min_error_statement = ERROR
-- log_min_messages = WARNING

-- Test NULL parameter handling directly in psql
PREPARE test_stmt (text) AS
    SELECT * FROM employees WHERE name = $1;

-- Pass explicit NULL safely
EXECUTE test_stmt(NULL);

-- Valid ODBC indicator constants (for reference):
-- SQL_NULL_DATA  = -1   → represents NULL
-- SQL_NTS        = -3   → null-terminated string
-- SQL_NO_TOTAL   = -4   → unknown length
-- Any other negative value → triggers error 22010

3. Improper NULL Handling in Dynamic Queries

In applications that construct dynamic SQL, failing to explicitly handle NULL values before binding parameters can result in invalid indicator values being transmitted to the server.

-- Safe NULL handling using server-side logic
CREATE OR REPLACE FUNCTION safe_upsert_employee(
    p_id INTEGER,
    p_name TEXT,
    p_dept TEXT
)
RETURNS VOID AS $$
BEGIN
    INSERT INTO employees (id, name, department)
    VALUES (
        p_id,
        COALESCE(p_name, 'Unknown'),   -- handle NULL explicitly
        NULLIF(p_dept, '')             -- treat empty string as NULL
    )
    ON CONFLICT (id) DO UPDATE
        SET name       = EXCLUDED.name,
            department = EXCLUDED.department;
EXCEPTION
    WHEN SQLSTATE '22010' THEN
        RAISE WARNING 'Invalid indicator for id=%: %', p_id, SQLERRM;
END;
$$ LANGUAGE plpgsql;

-- Dynamic query with explicit NULL branching
DO $$
DECLARE
    v_filter TEXT := NULL;
    v_sql    TEXT;
BEGIN
    IF v_filter IS NULL THEN
        v_sql := 'SELECT * FROM employees WHERE name IS NULL';
    ELSE
        v_sql := format('SELECT * FROM employees WHERE name = %L', v_filter);
    END IF;
    EXECUTE v_sql;
END;
$$;

Quick Fix Solutions

• ECPG: Always initialize indicator variables immediately upon declaration. Use only -1 for NULL and 0 for valid data.
• ODBC: Use only standard ODBC constants (SQL_NULL_DATA, SQL_NTS) for StrLen_or_IndPtr. Never pass arbitrary negative integers.
• Application Layer: Wrap all parameter binding logic in NULL-safe utility functions and validate indicator values before submitting queries.

-- Quick diagnostic: check active queries for anomalies
SELECT pid, usename, query_start, state, query
FROM pg_stat_activity
WHERE state = 'active'
  AND query NOT ILIKE '%pg_stat_activity%';

Prevention Tips

1. Enforce Coding Standards: Add indicator variable validation to your code review checklist and integrate static analysis tools (e.g., Cppcheck) into your CI/CD pipeline to catch invalid indicator usage automatically before deployment.

2. Enable Detailed Server Logging: Set log_min_error_statement = ERROR in postgresql.conf and use tools like pgBadger or pg_stat_statements to monitor for 22010 errors in production, enabling rapid identification of the offending queries and client modules.

Related Errors

Code	Name	Relation
22000	data_exception	Parent error class of 22010
22002	null_value_no_indicator_parameter	Opposite scenario: NULL returned without an indicator
22003	numeric_value_out_of_range	Often confused with indicator range violations
07002	too_few_arguments	Can co-occur in incomplete ODBC parameter binding

---

📖 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.