PostgreSQL 39004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 39004: null value not allowed

PostgreSQL error 39004 (null_value_not_allowed) occurs inside PL/pgSQL functions or procedures when a NULL value is assigned to a variable declared with NOT NULL, or when a function violates a NOT NULL contract on its return type. This error is distinct from the table-level 23502 error — it lives entirely within procedural code. Understanding where NULL values can sneak in is the key to resolving and preventing this error.

Top 3 Causes

1. Assigning NULL to a NOT NULL Variable

When a SELECT INTO query returns no rows or a NULL column value, and the target variable was declared NOT NULL, PostgreSQL raises 39004 immediately.

-- Triggers 39004 when user_id doesn't exist
CREATE OR REPLACE FUNCTION get_score(p_id INT)
RETURNS INT AS $$
DECLARE
    v_score INT NOT NULL := 0;
BEGIN
    SELECT score INTO v_score   -- Returns NULL if no row found
    FROM users
    WHERE user_id = p_id;
    RETURN v_score;
END;
$$ LANGUAGE plpgsql;

Quick Fix: Use COALESCE and handle the NOT FOUND case:

SELECT COALESCE(score, 0) INTO v_score
FROM users
WHERE user_id = p_id;

IF NOT FOUND THEN
    v_score := 0;
END IF;

2. STRICT Mode with No Data or Multiple Rows

SELECT INTO STRICT requires exactly one row. When combined with a NOT NULL variable, a missing row causes the assignment to attempt NULL, triggering 39004.

-- Raises error if order_id doesn't exist
CREATE OR REPLACE FUNCTION fetch_order_status(p_order_id INT)
RETURNS TEXT AS $$
DECLARE
    v_status TEXT NOT NULL := 'UNKNOWN';
BEGIN
    SELECT status INTO STRICT v_status
    FROM orders
    WHERE order_id = p_order_id;
    RETURN v_status;
END;
$$ LANGUAGE plpgsql;

Quick Fix: Wrap with an EXCEPTION block:

BEGIN
    SELECT status INTO STRICT v_status
    FROM orders
    WHERE order_id = p_order_id;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        v_status := 'NOT_FOUND';
    WHEN TOO_MANY_ROWS THEN
        RAISE EXCEPTION 'Duplicate orders for id: %', p_order_id;
END;

3. Returning NULL from a Function Using a NOT NULL Domain Type

If a function's return type is a domain defined with NOT NULL, returning a NULL value from the function will raise 39004.

-- Domain with NOT NULL constraint
CREATE DOMAIN positive_int AS INT NOT NULL CHECK (VALUE > 0);

CREATE OR REPLACE FUNCTION get_bonus(p_emp_id INT)
RETURNS positive_int AS $$
DECLARE
    v_bonus positive_int;
BEGIN
    SELECT bonus INTO v_bonus   -- NULL bonus triggers 39004
    FROM employees
    WHERE emp_id = p_emp_id;
    RETURN v_bonus;
END;
$$ LANGUAGE plpgsql;

Quick Fix: Validate before assigning to the domain type:

DECLARE
    v_raw INT;
    v_bonus positive_int;
BEGIN
    SELECT bonus INTO v_raw FROM employees WHERE emp_id = p_emp_id;

    IF v_raw IS NULL OR v_raw <= 0 THEN
        RAISE EXCEPTION 'Bonus is null or invalid for employee %', p_emp_id;
    END IF;

    v_bonus := v_raw;
    RETURN v_bonus;
END;

Prevention Tips

1. Always pair NOT NULL variables with meaningful defaults and COALESCE
Never declare a NOT NULL variable without a safe default, and always sanitize query results with COALESCE before assignment. This single habit eliminates the majority of 39004 occurrences.

DECLARE
    v_total NUMERIC NOT NULL := 0.0;
BEGIN
    SELECT COALESCE(SUM(amount), 0.0) INTO v_total
    FROM payments
    WHERE status = 'COMPLETED';
END;

2. Test edge cases before deploying functions
Always test your PL/pgSQL functions with NULL inputs, non-existent IDs, and empty result sets before pushing to production. Use pgTAP or simple DO blocks to automate these checks and catch 39004 vulnerabilities early in the development cycle.

Related Errors

Error Code	Name	Description
`23502`	`not_null_violation`	Table-level NOT NULL constraint violation on INSERT/UPDATE
`02000`	`no_data_found`	STRICT query returned zero rows
`21000`	`cardinality_violation`	STRICT query returned more than one row
`42804`	`datatype_mismatch`	Return type mismatch, often co-occurs 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.

DEV Community