PostgreSQL 22002 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22002: null value no indicator parameter

PostgreSQL error code 22002 (null_value_no_indicator_parameter) occurs when a query returns a NULL value into a host variable that has no associated indicator variable to handle it. This is most commonly seen in embedded SQL environments (ECPG), ODBC-based applications, or legacy C/C++ code that interacts directly with PostgreSQL. Without an indicator variable, the runtime has no safe place to signal that a NULL was received, so PostgreSQL raises this error to prevent silent data corruption.

Top 3 Causes

1. Missing Indicator Variable in ECPG (Embedded SQL in C)

When using ECPG, every host variable that could receive a NULL must have a paired indicator variable. Omitting it triggers error 22002 as soon as a NULL is fetched.

-- Problematic: No indicator variable declared
EXEC SQL SELECT salary INTO :emp_salary FROM employees WHERE emp_id = :id;

-- Correct: Declare and use an indicator variable
EXEC SQL BEGIN DECLARE SECTION;
    int emp_salary;
    short emp_salary_ind;  /* -1 = NULL, 0 = valid value */
EXEC SQL END DECLARE SECTION;

EXEC SQL SELECT salary
         INTO :emp_salary INDICATOR :emp_salary_ind
         FROM employees WHERE emp_id = :id;

-- Then check in C code:
-- if (emp_salary_ind == -1) { /* handle NULL */ }

2. Query Returns NULL from a Nullable Column

If a column has no NOT NULL constraint, it can contain NULL at any time. Code written under the assumption that data always exists will break in production when real NULL values appear.

-- This column allows NULL — dangerous without an indicator
SELECT bonus FROM employees WHERE emp_id = 1001;

-- Safer: Use COALESCE to substitute a default value
SELECT COALESCE(bonus, 0.00) AS bonus
FROM employees WHERE emp_id = 1001;

-- Multi-column example
SELECT
    emp_id,
    COALESCE(first_name, '')        AS first_name,
    COALESCE(salary, 0)             AS salary,
    COALESCE(department, 'N/A')     AS department
FROM employees
WHERE hire_date >= '2023-01-01';

3. Poor Table Design — No NOT NULL Constraints or Defaults

Tables created without explicit NOT NULL constraints and DEFAULT values are silent time bombs. Any insert that omits a column will store NULL, eventually triggering 22002 in connected applications.

-- Risky table design
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    customer   VARCHAR(100),   -- NULL allowed by default!
    total      NUMERIC(12,2)   -- NULL allowed by default!
);

-- Safe table design with constraints and defaults
CREATE TABLE orders (
    order_id   SERIAL PRIMARY KEY,
    customer   VARCHAR(100)  NOT NULL DEFAULT 'Guest',
    total      NUMERIC(12,2) NOT NULL DEFAULT 0.00,
    created_at TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Audit existing tables for unexpected NULLable columns
SELECT column_name, is_nullable, column_default
FROM information_schema.columns
WHERE table_name   = 'orders'
  AND table_schema = 'public'
ORDER BY ordinal_position;

Quick Fix Solutions

Option A — Add COALESCE to your query (fastest fix for legacy code):

SELECT COALESCE(salary, 0), COALESCE(notes, '') FROM employees;

Option B — Add NOT NULL + DEFAULT to existing columns:

ALTER TABLE employees
    ALTER COLUMN salary SET NOT NULL,
    ALTER COLUMN salary SET DEFAULT 0;

Option C — Find all NULLs before they hit your app:

SELECT COUNT(*) FILTER (WHERE salary IS NULL)   AS null_salary,
       COUNT(*) FILTER (WHERE bonus  IS NULL)   AS null_bonus
FROM employees;

Prevention Tips

Always declare indicator variables in ECPG/ODBC. Make it a mandatory code-review checklist item for any embedded SQL or ODBC code that fetches data. Never bind a host variable to a nullable column without a corresponding indicator buffer.
Enforce NOT NULL + DEFAULT at the schema level. During table design, explicitly decide whether each column should allow NULL. If not, add NOT NULL DEFAULT <value> immediately. Run periodic audits using information_schema.columns to catch nullable columns that sneak in over time.

Related Errors

Code	Name	Brief Description
22001	`string_data_right_truncation`	String too long for target column
22003	`numeric_value_out_of_range`	Number exceeds target type range
22004	`null_value_not_allowed`	NULL not permitted in this context
42804	`datatype_mismatch`	Host variable type doesn't match column type

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

DEV Community