DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

PostgreSQL 22004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 22004: null value not allowed

PostgreSQL error code 22004 — "null value not allowed" — occurs when a NULL value is passed to a context that explicitly prohibits it, such as a STRICT function, a domain type with a NOT NULL constraint, or certain array operations. Unlike error 23502 (not_null_violation), which applies at the table column level, error 22004 is raised at the type or function parameter level. Understanding the distinction is key to diagnosing and fixing it quickly.

Top 3 Causes

1. Passing NULL to a STRICT Function

When a function is defined with the STRICT keyword, PostgreSQL automatically returns NULL if any argument is NULL. However, in some configurations or PL/pgSQL blocks, this can surface as a 22004 error.

-- STRICT function definition
CREATE OR REPLACE FUNCTION multiply(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
    RETURN a * b;
END;
$$;

-- Triggers 22004 or returns NULL unexpectedly
SELECT multiply(5, NULL);

-- Fix: Remove STRICT and handle NULL explicitly
CREATE OR REPLACE FUNCTION multiply_safe(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF a IS NULL OR b IS NULL THEN
        RETURN 0;  -- or return NULL based on business logic
    END IF;
    RETURN a * b;
END;
$$;

SELECT multiply_safe(5, NULL);  -- Returns 0, no error
Enter fullscreen mode Exit fullscreen mode

2. Inserting NULL into a Domain Type with NOT NULL Constraint

User-defined domain types can carry a NOT NULL constraint. Any attempt to assign NULL to a column or variable of that domain type raises 22004.

-- Create a domain with NOT NULL
CREATE DOMAIN positive_amount AS NUMERIC NOT NULL CHECK (VALUE >= 0);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    amount positive_amount
);

-- Raises ERROR 22004
INSERT INTO orders (amount) VALUES (NULL);

-- Fix: Use COALESCE before casting
INSERT INTO orders (amount)
SELECT COALESCE(NULL, 0)::positive_amount;  -- Inserts 0 safely

-- Or alter the domain if NULL should be allowed
ALTER DOMAIN positive_amount DROP NOT NULL;
Enter fullscreen mode Exit fullscreen mode

3. NULL Elements in a Domain-Typed Array

Inserting an array that contains NULL elements into a column typed as an array of a NOT NULL domain will also trigger 22004.

-- Domain with NOT NULL
CREATE DOMAIN strict_int AS INTEGER NOT NULL;

CREATE TABLE batch_data (
    id SERIAL PRIMARY KEY,
    values strict_int[]
);

-- Raises ERROR 22004 due to NULL in array
INSERT INTO batch_data (values) VALUES (ARRAY[1, 2, NULL]::strict_int[]);

-- Fix: Remove NULLs before inserting
INSERT INTO batch_data (values)
VALUES (ARRAY_REMOVE(ARRAY[1, 2, NULL, 3], NULL)::strict_int[]);

-- Verify domain constraints
SELECT domain_name, is_nullable
FROM information_schema.domains
WHERE domain_schema = 'public';
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

  • Use COALESCE to replace NULL with a safe default before passing values to domain-typed columns or STRICT functions.
  • Handle exceptions in PL/pgSQL using the null_value_not_allowed condition name.
  • Remove STRICT from function definitions and implement explicit NULL checks inside the function body. 
-- Exception handling example
DO $$
BEGIN
    PERFORM some_strict_function(NULL);
EXCEPTION
    WHEN null_value_not_allowed THEN
        RAISE NOTICE 'Caught 22004: NULL value was not allowed here.';
END;
$$;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Use a staging table with plain types to validate and clean data before loading it into tables with domain-typed columns. This creates a safe buffer zone where NULLs can be caught and handled before they cause errors.

  2. Document domain types with COMMENT and always run NULL-scenario tests in a QA environment before deploying schema changes. Track domain dependencies using the pg_attribute and pg_type catalog tables to understand the blast radius of any constraint change.

-- Find all columns using a specific domain
SELECT c.relname AS table_name, a.attname AS column_name
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
WHERE t.typname = 'positive_amount'
  AND c.relkind = 'r';
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • 23502 not_null_violation — Column-level NOT NULL constraint breach; often confused with 22004.
  • 22023 invalid_parameter_value — Invalid value passed to a function parameter.
  • 42804 datatype_mismatch — Type mismatch when working with domain types.

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