PostgreSQL Null Value Not Allowed (SQLSTATE 22004)

PostgreSQL raises ERROR: null value not allowed with SQLSTATE 22004 and condition name null_value_not_allowed when a NULL value is supplied to a context that explicitly forbids it — most commonly certain built-in functions, window function parameters, or PL/pgSQL constructs that require a non-null argument to produce a meaningful result.

What This Error Means

SQLSTATE 22004 belongs to error class 22 — Data Exception — the same class as divide-by-zero (22012), string data right truncation (22001), and invalid datetime format (22007). Errors in this class signal that valid SQL was executed but the runtime data values violated a constraint imposed by the SQL standard or by PostgreSQL itself.

Unlike the more common 23502 (not_null_violation), which fires when you try to store a NULL in a column defined as NOT NULL, SQLSTATE 22004 applies to situations where a NULL is passed as a functional argument or context value where NULL is semantically meaningless or explicitly prohibited. The distinction is important: 22004 is about what you pass to an operation, not what you store in a column.

When this error is raised, the current statement is aborted and the transaction is placed in an error state. In an explicit transaction block you must issue a ROLLBACK (or ROLLBACK TO SAVEPOINT) before you can execute further statements. Outside a transaction block the error is contained to the single statement.

Common Causes

1. Passing NULL as the OFFSET or FETCH value in a window function or LIMIT/OFFSET clause. PostgreSQL requires a non-null, non-negative integer for row-count parameters. Passing a NULL (e.g., from a variable or subquery that returned no rows) triggers 22004.

2. NULL passed to a PL/pgSQL EXECUTE ... USING parameter where the parameterised query context does not permit NULL. Some dynamic SQL constructs raise this when a bind variable that feeds a structural position (not a data position) resolves to NULL.

3. User-defined functions or procedures with a STRICT marking. A function declared STRICT (or CALLED ON NULL INPUT overridden to RETURNS NULL ON NULL INPUT) returns NULL automatically without raising 22004, but certain internal functions implemented in C raise 22004 directly when they encounter NULL in a required argument slot.

4. NULL supplied to certain range or array constructor functions where a non-null boundary is required to construct a valid value.

How to Fix null_value_not_allowed

1. Guard against NULL before using the value as a parameter. Use COALESCE or a CASE expression to substitute a safe default:

-- Instead of this (will error if :offset_val is NULL):
SELECT * FROM orders OFFSET :offset_val;

-- Do this:
SELECT * FROM orders OFFSET COALESCE(:offset_val, 0);

2. Check function arguments in PL/pgSQL before passing them. Add an explicit NULL check at the top of any function that feeds values into positions that forbid NULL:

CREATE OR REPLACE FUNCTION paginate_results(p_offset INT)
RETURNS SETOF orders AS $$
BEGIN
  IF p_offset IS NULL THEN
    RAISE EXCEPTION 'p_offset must not be NULL';
  END IF;
  RETURN QUERY SELECT * FROM orders OFFSET p_offset;
END;
$$ LANGUAGE plpgsql;

3. Trace the NULL back to its origin. Run the offending query with literal values to confirm it succeeds, then narrow down which bind parameter or subquery is returning NULL unexpectedly. A subquery with no matching rows returns NULL rather than zero — wrap it with COALESCE:

-- Subquery can return NULL if no rows match:
SELECT * FROM orders
OFFSET (SELECT MAX(seen_offset) FROM session_state WHERE session_id = $1);

-- Safe version:
SELECT * FROM orders
OFFSET COALESCE(
  (SELECT MAX(seen_offset) FROM session_state WHERE session_id = $1),
  0
);

4. Review dynamic SQL in PL/pgSQL. If you are building and executing dynamic SQL with EXECUTE ... USING, ensure every value in the USING list is non-null where the generated SQL requires it. Print the generated SQL with RAISE NOTICE '%', sql_string; during development to verify the structure.

Additional Information

• SQLSTATE 22004 is defined in the SQL standard and has been part of PostgreSQL since at least version 7.4. Its behaviour has not changed significantly across versions.
• Related codes in error class 22 include 22002 (null_value_no_indicator_parameter), 22003 (numeric_value_out_of_range), and 22012 (division_by_zero).
• Most PostgreSQL client drivers (libpq, psycopg2, asyncpg, JDBC, node-postgres) surface this as a DatabaseError or equivalent with pgcode == "22004" available on the exception object — check your driver's documentation for the exact attribute name.
• This error is relatively rare in typical CRUD applications. It appears more frequently in reporting queries and analytical workloads that use window functions with dynamic parameters, or in stored procedure frameworks that generate and execute SQL dynamically.

Frequently Asked Questions

Is 22004 the same as a NOT NULL constraint violation? No. A NOT NULL constraint violation has SQLSTATE 23502 (not_null_violation) and occurs when you try to insert or update a column defined as NOT NULL with a NULL value. SQLSTATE 22004 fires when a NULL is passed to a function parameter or operational context where NULL is not permitted, regardless of any column definitions.

Why does my query work with a literal value but fail when I use a variable? The literal value is never NULL, but the variable may be. Common sources of unexpected NULLs include subqueries that match zero rows, MAX()/MIN() over an empty set, and application-level parameters that were not initialised before the query ran.

Does wrapping the call in a STRICT function prevent this error? Not exactly. A function declared with RETURNS NULL ON NULL INPUT (or STRICT) will silently return NULL rather than executing its body when any argument is NULL. This prevents the body from running and avoids the 22004 raised inside it, but it also means your function returns NULL instead of a meaningful result. Use this only when a NULL result is an acceptable outcome for NULL inputs.

How do I find which parameter is NULL at runtime? Add RAISE NOTICE statements before the offending operation to log each candidate value, or wrap the statement in a PL/pgSQL block and catch the exception to log context:

DO $$
DECLARE
  v_offset INT := NULL; -- example
BEGIN
  RAISE NOTICE 'offset value: %', v_offset;
  PERFORM * FROM orders OFFSET v_offset;
EXCEPTION
  WHEN null_value_not_allowed THEN
    RAISE NOTICE 'Caught 22004 — offset was NULL';
END;
$$;