PostgreSQL 20000 Error: Causes and Solutions Complete Guide

#postgres #database #dba #sql

PostgreSQL Error 20000: case_not_found — What It Means and How to Fix It

PostgreSQL error code 20000, known as case_not_found, is a runtime error thrown inside PL/pgSQL when a CASE statement has no matching WHEN clause for the given input value and no ELSE clause is defined. Unlike a plain SQL CASE expression (which simply returns NULL when no condition matches), a PL/pgSQL CASE statement requires a matching branch to exist — otherwise it raises this exception immediately. Understanding the distinction is critical for writing robust database functions and stored procedures.

Top 3 Causes

1. Missing ELSE Clause

The most common cause is simply forgetting to add an ELSE clause to a PL/pgSQL CASE statement. When an unexpected value arrives — even just once — the entire function fails.

-- ❌ Problematic: No ELSE clause
CREATE OR REPLACE FUNCTION get_label(p_code TEXT)
RETURNS TEXT AS $$
BEGIN
    CASE p_code
        WHEN 'X' THEN RETURN 'Express';
        WHEN 'S' THEN RETURN 'Standard';
        -- Any other value raises ERROR 20000!
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- ✅ Fixed: ELSE clause added
CREATE OR REPLACE FUNCTION get_label(p_code TEXT)
RETURNS TEXT AS $$
BEGIN
    CASE p_code
        WHEN 'X' THEN RETURN 'Express';
        WHEN 'S' THEN RETURN 'Standard';
        ELSE RETURN 'Unknown';  -- Safe fallback
    END CASE;
END;
$$ LANGUAGE plpgsql;

2. New Data Values After Deployment

Business requirements change. New status codes, categories, or types get added to the database, but the PL/pgSQL functions are never updated to handle them. This is one of the most dangerous causes because the function worked perfectly before and suddenly starts failing in production.

-- Suppose 'REFUNDED' was added to order statuses after deployment
CREATE OR REPLACE FUNCTION process_order(p_status TEXT)
RETURNS TEXT AS $$
BEGIN
    CASE p_status
        WHEN 'PENDING'   THEN RETURN 'Awaiting approval';
        WHEN 'APPROVED'  THEN RETURN 'Processing';
        WHEN 'SHIPPED'   THEN RETURN 'On the way';
        -- 'REFUNDED' was added later — triggers ERROR 20000
        ELSE
            -- Log unknown values for monitoring
            RAISE WARNING 'Unhandled status encountered: %', p_status;
            RETURN 'Status: ' || COALESCE(p_status, 'NULL');
    END CASE;
END;
$$ LANGUAGE plpgsql;

3. NULL Values Not Handled

In PL/pgSQL, NULL does not match any WHEN condition because NULL = NULL evaluates to NULL, not TRUE. If a column lacks a NOT NULL constraint, a NULL value will bypass every WHEN clause and trigger case_not_found.

-- ❌ NULL input causes ERROR 20000
CREATE OR REPLACE FUNCTION score_grade(p_score NUMERIC)
RETURNS TEXT AS $$
BEGIN
    CASE
        WHEN p_score >= 90 THEN RETURN 'A';
        WHEN p_score >= 80 THEN RETURN 'B';
        WHEN p_score >= 70 THEN RETURN 'C';
        -- NULL p_score matches NONE of the above!
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- ✅ NULL handled explicitly
CREATE OR REPLACE FUNCTION score_grade(p_score NUMERIC)
RETURNS TEXT AS $$
BEGIN
    CASE
        WHEN p_score IS NULL THEN RETURN 'No Score';
        WHEN p_score >= 90   THEN RETURN 'A';
        WHEN p_score >= 80   THEN RETURN 'B';
        WHEN p_score >= 70   THEN RETURN 'C';
        ELSE                      RETURN 'F';
    END CASE;
END;
$$ LANGUAGE plpgsql;

Quick Fix: EXCEPTION Block

If you can't immediately refactor the function, wrap the logic in an EXCEPTION block to catch case_not_found gracefully:

CREATE OR REPLACE FUNCTION safe_get_label(p_code TEXT)
RETURNS TEXT AS $$
BEGIN
    CASE p_code
        WHEN 'X' THEN RETURN 'Express';
        WHEN 'S' THEN RETURN 'Standard';
    END CASE;
EXCEPTION
    WHEN case_not_found THEN
        RAISE WARNING 'case_not_found for input: %', p_code;
        RETURN 'Fallback Value';
END;
$$ LANGUAGE plpgsql;

Prevention Tips

Always include an ELSE clause. Treat it as a non-negotiable coding standard for every PL/pgSQL CASE statement. Even if you believe all values are covered, an ELSE with a RAISE WARNING or default return value prevents unexpected production failures.

Write boundary and NULL tests before deploying. Use pgTAP or a similar framework to test your functions with out-of-range values, NULL, and empty strings. Automate these tests in your CI/CD pipeline so any new data values that break existing logic are caught before they reach production.

-- Quick pgTAP smoke test example
SELECT plan(3);
SELECT is(safe_get_label('X'),    'Express',        'Known code X works');
SELECT is(safe_get_label(NULL),   'Fallback Value', 'NULL handled safely');
SELECT is(safe_get_label('ZZZZ'), 'Fallback Value', 'Unknown code handled');
SELECT * FROM finish();

Related Errors

Code	Name	Notes
`P0001`	`raise_exception`	Explicit exception raised via `RAISE EXCEPTION`
`P0002`	`no_data_found`	`SELECT INTO` returns no rows — common in the same PL/pgSQL context
`P0003`	`too_many_rows`	`SELECT INTO` returns multiple rows

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