PostgreSQL 39004 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 39004: null value not allowed

PostgreSQL error 39004 occurs within procedural language functions (PL/pgSQL, PL/Perl, PL/Python) when a NULL value is assigned to a variable or parameter that explicitly disallows NULL. This typically surfaces inside stored procedures or user-defined functions where NOT NULL variable declarations or strict NULL-handling logic is in place. Understanding this error requires knowing where NULL enters your function's execution flow and how your variable declarations respond to it.

Top 3 Causes

1. Assigning NULL to a NOT NULL Declared Variable

The most common cause is declaring a PL/pgSQL variable with NOT NULL and then letting a NULL value flow into it at runtime.

-- Problematic function
CREATE OR REPLACE FUNCTION get_discounted_price(p_price NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    v_result NUMERIC NOT NULL := 0;  -- NOT NULL variable
BEGIN
    -- If p_price is NULL, this assignment triggers error 39004
    v_result := p_price * 0.9;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

-- This call triggers 39004
SELECT get_discounted_price(NULL);

-- Fix: Use COALESCE to guard against NULL input
CREATE OR REPLACE FUNCTION get_discounted_price(p_price NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    v_result NUMERIC NOT NULL := 0;
BEGIN
    v_result := COALESCE(p_price, 0) * 0.9;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

2. SELECT INTO Returning NULL into a NOT NULL Variable

When a SELECT INTO statement retrieves no matching row or a NULL column value, and the target variable is declared NOT NULL, error 39004 is raised immediately.

-- Problematic procedure
CREATE OR REPLACE PROCEDURE process_employee(p_emp_id INT)
LANGUAGE plpgsql AS $$
DECLARE
    v_salary NUMERIC NOT NULL := 0;
BEGIN
    -- If salary column is NULL in the table, error 39004 fires
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = p_emp_id;

    RAISE NOTICE 'Salary: %', v_salary;
END;
$$;

-- Fix: Remove NOT NULL from the variable, then handle NULL explicitly
CREATE OR REPLACE PROCEDURE process_employee(p_emp_id INT)
LANGUAGE plpgsql AS $$
DECLARE
    v_salary NUMERIC;  -- Allow NULL at variable level
BEGIN
    SELECT salary INTO v_salary
    FROM employees
    WHERE emp_id = p_emp_id;

    IF v_salary IS NULL THEN
        RAISE WARNING 'No salary found for emp_id %. Using default.', p_emp_id;
        v_salary := 0;
    END IF;

    RAISE NOTICE 'Salary: %', v_salary;
END;
$$;

3. NULL Values in Loop/Cursor Processing

Batch processing procedures that iterate over records are vulnerable when individual record fields are NULL and get assigned to NOT NULL variables inside the loop.

-- Problematic batch procedure
CREATE OR REPLACE PROCEDURE recalculate_totals()
LANGUAGE plpgsql AS $$
DECLARE
    v_rec   RECORD;
    v_total NUMERIC NOT NULL := 0;
    v_line  NUMERIC NOT NULL := 0;
BEGIN
    FOR v_rec IN SELECT order_id, unit_price, qty FROM order_items LOOP
        -- Triggers 39004 if unit_price or qty is NULL
        v_line  := v_rec.unit_price * v_rec.qty;
        v_total := v_total + v_line;
    END LOOP;
    RAISE NOTICE 'Grand Total: %', v_total;
END;
$$;

-- Fix: Use COALESCE directly in the SELECT or add NULL guards
CREATE OR REPLACE PROCEDURE recalculate_totals()
LANGUAGE plpgsql AS $$
DECLARE
    v_rec   RECORD;
    v_total NUMERIC NOT NULL := 0;
    v_line  NUMERIC NOT NULL := 0;
BEGIN
    FOR v_rec IN
        SELECT order_id,
               COALESCE(unit_price, 0) AS unit_price,
               COALESCE(qty, 0)        AS qty
        FROM order_items
    LOOP
        v_line  := v_rec.unit_price * v_rec.qty;
        v_total := v_total + v_line;
    END LOOP;
    RAISE NOTICE 'Grand Total: %', v_total;
END;
$$;

Quick Fix Solutions

Use COALESCE to substitute NULL with a safe default before assignment.
Declare variables without NOT NULL unless you truly need that constraint, then validate after assignment.
Use STRICT on functions that should return NULL when any argument is NULL — this prevents execution entirely rather than erroring mid-function.

-- STRICT example: function simply returns NULL when input is NULL
CREATE OR REPLACE FUNCTION safe_square(p_val NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN p_val * p_val;
END;
$$ LANGUAGE plpgsql STRICT;

SELECT safe_square(NULL);  -- Returns NULL cleanly, no error
SELECT safe_square(9);     -- Returns 81

Prevention Tips

Establish a NULL-handling convention for all UDFs. Decide upfront whether each function uses STRICT or handles NULL internally, and document it. Avoid mixing NOT NULL variable declarations with unchecked external inputs.
Add NULL boundary tests to your CI pipeline. Use a framework like pgTAP to automatically test every function with NULL inputs before deployment. Catching 39004 in development is far cheaper than debugging it in production at 2 AM.

-- pgTAP null boundary test example
SELECT plan(2);
SELECT is(safe_square(NULL), NULL,       'NULL input returns NULL');
SELECT is(safe_square(4),    16::NUMERIC, 'Normal input works correctly');
SELECT * FROM finish();

Related Errors

22004 (null_value_not_allowed): Similar concept at the SQL layer rather than the procedural language layer.
23502 (not_null_violation): Triggered when inserting/updating a NULL into a table column with a NOT NULL constraint — more common in day-to-day DBA work.
39001 (external_routine_exception): The parent error class for 39004; all procedural language runtime errors fall under this category.

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