null value not allowed

Category: Data ExceptionVersions: All Postgres versions

What this means

SQLSTATE 22004 is raised when a NULL value is provided to a function or context that explicitly requires a non-NULL value. This differs from 23502 (NOT NULL constraint violation on a column) — 22004 applies to function arguments and specific expression contexts.

Why it happens

1Passing NULL to a function parameter declared as STRICT (in PL/pgSQL STRICT functions return NULL immediately, but some contexts raise 22004)
2A domain with a NOT NULL constraint receives NULL
3Built-in functions that explicitly prohibit NULL arguments

How to reproduce

NULL value passed where explicitly not allowed.

trigger — this will ERROR

CREATE DOMAIN positive_int AS INTEGER NOT NULL CHECK (VALUE > 0);
INSERT INTO t (col) VALUES (NULL::positive_int);

ERROR: domain positive_int does not allow null values

Fix 1: Provide a non-NULL value or a default

When inserting or passing values to contexts that forbid NULL.

fix

INSERT INTO t (col) VALUES (COALESCE(source_val, 1));

Why this works

COALESCE substitutes a default value when the source is NULL, preventing 22004.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

🔧 Source ref: Class 22 — Data Exception

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE for null-not-allowed in function/domain contexts. Stable across versions.