PostgreSQL 20000 Error: Causes and Solutions Complete Guide

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

PostgreSQL error code 20000 (case_not_found) is raised in PL/pgSQL when a CASE statement executes but none of its WHEN clauses match the given value, and no ELSE clause is present to handle the default scenario. Unlike the SQL CASE expression (which simply returns NULL when no branch matches), the PL/pgSQL CASE statement throws a runtime exception. This makes it a common source of unexpected production failures, especially as data evolves over time.

---

Top 3 Causes

1. Missing ELSE Clause with Unexpected Input Values

The most frequent cause: a CASE statement written without an ELSE clause encounters a value not covered by any WHEN condition.

-- This will raise ERROR 20000 if p_status = 'D'
CREATE OR REPLACE FUNCTION get_label(p_status TEXT)
RETURNS TEXT AS $$
DECLARE v_label TEXT;
BEGIN
    CASE p_status
        WHEN 'A' THEN v_label := 'Active';
        WHEN 'B' THEN v_label := 'Inactive';
        -- No ELSE: 'D' causes case_not_found!
    END CASE;
    RETURN v_label;
END;
$$ LANGUAGE plpgsql;

-- Fix: Always add an ELSE clause
CREATE OR REPLACE FUNCTION get_label(p_status TEXT)
RETURNS TEXT AS $$
DECLARE v_label TEXT;
BEGIN
    CASE p_status
        WHEN 'A' THEN v_label := 'Active';
        WHEN 'B' THEN v_label := 'Inactive';
        ELSE v_label := 'Unknown';  -- Safe fallback
    END CASE;
    RETURN v_label;
END;
$$ LANGUAGE plpgsql;

2. Unhandled NULL Values

In PL/pgSQL's simple CASE form, NULL = NULL evaluates to FALSE, so NULL inputs will never match any WHEN clause, triggering the error.

-- NULL input causes case_not_found
CREATE OR REPLACE FUNCTION process_type(p_type TEXT)
RETURNS TEXT AS $$
DECLARE v_result TEXT;
BEGIN
    CASE p_type
        WHEN 'X' THEN v_result := 'Type X';
        WHEN 'Y' THEN v_result := 'Type Y';
        -- NULL never matches!
    END CASE;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

-- Fix: Handle NULL explicitly using searched CASE form
CREATE OR REPLACE FUNCTION process_type(p_type TEXT)
RETURNS TEXT AS $$
DECLARE v_result TEXT;
BEGIN
    CASE
        WHEN p_type IS NULL   THEN v_result := 'No Type';
        WHEN p_type = 'X'     THEN v_result := 'Type X';
        WHEN p_type = 'Y'     THEN v_result := 'Type Y';
        ELSE v_result := 'Other: ' || p_type;
    END CASE;
    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

3. New Enum/Code Values Added Without Updating Functions

As applications grow, new status or type codes get added to tables, but existing PL/pgSQL functions are not updated to handle them.

-- Fragile: adding a new status 'PENDING' breaks this function
CREATE OR REPLACE FUNCTION handle_order(p_status TEXT)
RETURNS VOID AS $$
BEGIN
    CASE p_status
        WHEN 'NEW'      THEN PERFORM process_new_order();
        WHEN 'SHIPPED'  THEN PERFORM process_shipped_order();
        -- New 'PENDING' status causes case_not_found!
    END CASE;
END;
$$ LANGUAGE plpgsql;

-- Robust fix: use EXCEPTION block as a safety net
CREATE OR REPLACE FUNCTION handle_order_safe(p_status TEXT)
RETURNS VOID AS $$
BEGIN
    CASE p_status
        WHEN 'NEW'      THEN PERFORM process_new_order();
        WHEN 'SHIPPED'  THEN PERFORM process_shipped_order();
        WHEN 'PENDING'  THEN PERFORM process_pending_order();
        ELSE
            RAISE EXCEPTION 'Unhandled order status: %', p_status
                USING ERRCODE = 'invalid_parameter_value';
    END CASE;
EXCEPTION
    WHEN case_not_found THEN
        RAISE WARNING 'Unhandled status caught: %', p_status;
END;
$$ LANGUAGE plpgsql;

---

Quick Fix Solutions

• Always add ELSE: Every PL/pgSQL CASE statement should have an ELSE clause — either returning a default value or raising a meaningful exception.
• Use EXCEPTION WHEN case_not_found: Wrap risky CASE blocks in an exception handler for graceful degradation without code refactoring.
• Prefer searched CASE form: Use CASE WHEN condition THEN ... instead of CASE variable WHEN value THEN ... to handle NULL and complex conditions more safely.

---

Prevention Tips

1. Enforce ELSE as a coding standard: Add a linting rule or code review checklist item requiring every PL/pgSQL CASE statement to include an ELSE clause. Regularly audit existing functions with a query against pg_proc.

2. Write boundary-value tests before deploying: Use a testing framework like pgTAP to test functions with NULL, empty strings, and unlisted values before every deployment. This catches case_not_found issues before they reach production.
   

-- Quick audit: find functions with CASE but no ELSE
SELECT proname
FROM pg_proc
WHERE prolang = (SELECT oid FROM pg_language WHERE lanname = 'plpgsql')
  AND prosrc ILIKE '%case%'
  AND prosrc NOT ILIKE '%else%'
  AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public');

---

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