DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22002: null value no indicator parameter

PostgreSQL error code 22002 occurs when a database column containing a NULL value is fetched into a host variable that has no associated indicator variable to signal the NULL condition. This error is most commonly encountered in Embedded SQL (ECPG) programs written in C, where the runtime cannot map a NULL database value to a plain C data type without an indicator. If left unhandled, it causes an abrupt runtime failure that can be difficult to diagnose in production.

Top 3 Causes

1. Missing Indicator Variable in ECPG Host Variable Binding

The most frequent cause is declaring a host variable for a nullable column without a paired indicator variable.

-- WRONG: No indicator variable for nullable salary column
EXEC SQL SELECT emp_name, salary
         INTO :emp_name, :salary       -- salary can be NULL!
         FROM employees WHERE emp_id = 1;

-- CORRECT: Declare and use indicator variables
EXEC SQL BEGIN DECLARE SECTION;
    char  emp_name[100];
    short emp_name_ind;   /* indicator for emp_name */
    float salary;
    short salary_ind;     /* indicator for salary */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT emp_name, salary
         INTO :emp_name :emp_name_ind, :salary :salary_ind
         FROM employees WHERE emp_id = 1;

-- Check NULL with indicator (< 0 means NULL)
if (salary_ind < 0) {
    printf("Salary is NULL\n");
}
Enter fullscreen mode Exit fullscreen mode

2. FETCH from Cursor with NULL Columns (e.g., OUTER JOINs)

Using a cursor to FETCH rows that include NULL values from LEFT/RIGHT JOINs or sparse tables without indicator variables triggers error 22002 at runtime.

-- Cursor that may produce NULL dept_name via LEFT JOIN
EXEC SQL DECLARE cur CURSOR FOR
    SELECT e.emp_id, e.emp_name, d.dept_name
    FROM employees e
    LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- CORRECT FETCH with indicator variables
EXEC SQL FETCH cur
    INTO :emp_id,
         :emp_name  :emp_name_ind,
         :dept_name :dept_name_ind;  /* dept_name can be NULL */

if (dept_name_ind < 0) {
    printf("No department assigned.\n");
}
Enter fullscreen mode Exit fullscreen mode

3. Avoid NULL at the Database Level Using COALESCE or NOT NULL Constraints

If refactoring the host application is not immediately feasible, you can prevent NULLs from reaching the host variable by substituting defaults in the query itself.

-- Use COALESCE to replace NULL with a safe default
SELECT
    emp_id,
    COALESCE(emp_name, 'Unknown')     AS emp_name,
    COALESCE(salary,   0.0)           AS salary,
    COALESCE(dept_name, 'N/A')        AS dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

-- Or enforce NOT NULL at the schema level
ALTER TABLE employees
    ALTER COLUMN salary SET NOT NULL,
    ALTER COLUMN salary SET DEFAULT 0.0;

-- Identify all nullable columns in a table
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_name = 'employees'
  AND is_nullable = 'YES'
ORDER BY ordinal_position;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Always pair indicator variables with every host variable that maps to a nullable column — make this a non-negotiable coding standard.
  • Use COALESCE in your SQL queries to substitute NULLs with sensible defaults when you cannot change the host program immediately.
  • Apply NOT NULL constraints at the schema level for columns that should never be NULL, preventing the issue at the data layer.
  • Audit your table schema with information_schema.columns to identify all nullable columns and ensure every corresponding host variable has an indicator.

Prevention Tips

  1. Include NULL data in your test suite. Every integration test should contain at least one row with NULL values in nullable columns. This catches missing indicator variables before they hit production.

  2. Add a code review checklist item. For any ECPG or low-level ODBC code, explicitly verify that every host variable bound to a nullable column has a corresponding indicator variable. Automate this check with static analysis tools where possible.

Related errors: **22001* (string data right truncation), 22003 (numeric value out of range), 22000 (data exception — parent class), 42804 (datatype mismatch).*

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

Top comments (0)