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) is raised when a NULL value is passed to a context that explicitly prohibits it — such as a NOT NULL column, a domain type with a null restriction, or a function parameter that does not accept NULL. Unlike the more common 23502 (not_null_violation), this error often surfaces in function calls and domain-level constraints, making it slightly trickier to diagnose. Understanding the root cause quickly is essential to maintaining data integrity and service stability.

Top 3 Causes

1. Inserting NULL into a NOT NULL Column

The most frequent cause is attempting to insert or update a NOT NULL column with a NULL value, either explicitly or by omitting the column in an INSERT statement.

-- Table definition
CREATE TABLE employees (
    emp_id   SERIAL PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    dept_id  INTEGER      NOT NULL
);

-- This will raise an error
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (1, 'Alice', NULL);
-- ERROR: null value in column "dept_id" violates not-null constraint

-- Fix: provide a valid value or use COALESCE
INSERT INTO employees (emp_id, emp_name, dept_id)
VALUES (1, 'Alice', COALESCE(NULL, 0));

-- Or set a default at the schema level
ALTER TABLE employees
    ALTER COLUMN dept_id SET DEFAULT 0;

2. Passing NULL to a STRICT Function

When a PostgreSQL function is defined with the STRICT keyword, any NULL argument causes the function to short-circuit and return NULL immediately. In some contexts — particularly inside PL/pgSQL blocks that expect a non-null result — this can trigger error 22004.

-- STRICT function example
CREATE OR REPLACE FUNCTION calculate_tax(amount NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
STRICT
AS $$
BEGIN
    RETURN amount * rate;
END;
$$;

-- Calling with NULL silently returns NULL due to STRICT
SELECT calculate_tax(500, NULL); -- returns NULL

-- Fix: Remove STRICT and handle NULL explicitly
CREATE OR REPLACE FUNCTION calculate_tax_safe(amount NUMERIC, rate NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    IF amount IS NULL OR rate IS NULL THEN
        RAISE EXCEPTION 'Arguments must not be NULL'
            USING ERRCODE = '22004';
    END IF;
    RETURN amount * rate;
END;
$$;

3. Domain Type with NOT NULL Constraint

PostgreSQL allows user-defined domain types to carry a NOT NULL constraint. Inserting NULL into a column using such a domain type raises 22004, which can be confusing if developers are unaware of the domain definition.

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

-- Use it in a table
CREATE TABLE invoices (
    invoice_id SERIAL PRIMARY KEY,
    amount     positive_amount
);

-- This fails with error 22004
INSERT INTO invoices (invoice_id, amount)
VALUES (1, NULL);
-- ERROR: domain positive_amount does not allow null values

-- Fix: Replace NULL with a default value
INSERT INTO invoices (invoice_id, amount)
VALUES (1, COALESCE(NULL, 0.01));

-- Or redefine the domain without NOT NULL
DROP DOMAIN positive_amount CASCADE;
CREATE DOMAIN positive_amount AS NUMERIC
    CHECK (VALUE > 0);

Quick Fix Solutions

-- 1. Check which columns are NOT NULL with no default
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = 'public'
  AND is_nullable = 'NO'
  AND column_default IS NULL;

-- 2. Use COALESCE to substitute NULLs before inserting
INSERT INTO orders (order_id, customer_id, status)
SELECT
    order_id,
    COALESCE(customer_id, 0),
    COALESCE(status, 'unknown')
FROM staging_orders;

-- 3. Add a DEFAULT to an existing NOT NULL column
ALTER TABLE orders
    ALTER COLUMN customer_id SET DEFAULT 0;

Prevention Tips

1. Always pair NOT NULL with a DEFAULT value during table design. This prevents accidental NULL insertions when columns are omitted in INSERT statements, and makes your schema self-documenting.

-- Recommended table design
CREATE TABLE orders (
    order_id    SERIAL       PRIMARY KEY,
    customer_id INTEGER      NOT NULL DEFAULT 0,
    status      VARCHAR(20)  NOT NULL DEFAULT 'pending',
    created_at  TIMESTAMPTZ  NOT NULL DEFAULT NOW()
);

2. Validate NULLs at the application layer before hitting the database. Treat the DB constraint as a last line of defense, not the first. Use input validation in your API or service layer to catch NULL values early, reducing round-trips and improving error messages for end users.

Related Errors

23502 (not_null_violation) — The closest sibling; triggered by NOT NULL column violations in standard DML operations.
22023 (invalid_parameter_value) — Raised when a function receives a parameter value that is technically non-null but still invalid in context.

📖 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