What causes "20000: case_not_found"?

CASE statement without an ELSE clause encountering unexpected input values Data changes that introduce new values not covered by existing WHEN conditions Boundary conditions or edge cases not accounted for in CASE logic

Is the "20000: case_not_found" error serious?

It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

How to fix 20000: case_not_found in PostgreSQL

PostgreSQLINTERMEDIATEMEDIUMUpdated Dec 22, 2025· 95 views

The PostgreSQL error "20000: case_not_found" occurs in PL/pgSQL when a CASE statement executes without matching any WHEN condition and no ELSE clause is provided. This runtime error indicates that the CASE expression failed to find a matching branch, causing the function or procedure to abort.

Quick fix

Fixes 20000: case_not_found

-- Example of problematic CASE without ELSE
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    -- No ELSE clause - will fail for status_code = 4
  END;
END;
$$ LANGUAGE plpgsql;

-- This will fail:
SELECT get_status_label(4);  -- Error: 20000: case_not_found

Identify the failing CASE statement
Add an ELSE clause to handle unexpected values
Handle NULL values explicitly
Validate input parameters before CASE

postgresql — error

$-- Example of problematic CASE without ELSE
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    -- No ELSE clause - will fail for status_code = 4
  END;
END;
$$ LANGUAGE plpgsql;
-- This will fail:
SELECT get_status_label(4);  -- Error: 20000: case_not_found
20000: case_not_found

What this error means

In PostgreSQL's PL/pgSQL language, CASE statements are used for conditional logic similar to switch statements in other programming languages. The error "20000: case_not_found" is raised when a CASE expression evaluates but none of the WHEN conditions match, and there is no ELSE clause to provide a default value. This is a runtime error that causes the current function or procedure to stop execution with an exception. The error code 20000 corresponds to the SQLSTATE "case_not_found", which is specific to PL/pgSQL's flow control errors.

Typical symptoms

PL/pgSQL function or procedure fails with error: "20000: case_not_found"
Database transactions are rolled back when this error occurs
Applications receiving PostgreSQL error: "case not found"
Stored procedures that work with some inputs but fail with others
Batch processing jobs aborting due to unhandled CASE conditions

Common causes

CASE statement without an ELSE clause encountering unexpected input values
Data changes that introduce new values not covered by existing WHEN conditions
Boundary conditions or edge cases not accounted for in CASE logic
Using CASE with variables that can be NULL without handling NULL explicitly
Migration of data that introduces new enum values or status codes

How to fix "20000: case_not_found"

1Identify the failing CASE statement

First, locate the PL/pgSQL function or procedure that's failing. Look for CASE statements without ELSE clauses:

sql

-- Example of problematic CASE without ELSE
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    -- No ELSE clause - will fail for status_code = 4
  END;
END;
$$ LANGUAGE plpgsql;

-- This will fail:
SELECT get_status_label(4);  -- Error: 20000: case_not_found

Check PostgreSQL logs or use SHOW ERRORS to find the exact function name.

2Add an ELSE clause to handle unexpected values

The most straightforward fix is to add an ELSE clause that provides a default value or raises a more informative error:

sql

-- Fix 1: Add a default ELSE value
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    ELSE 'Unknown'  -- Default for unexpected values
  END;
END;
$$ LANGUAGE plpgsql;

-- Fix 2: Raise a custom error with more context
CREATE OR REPLACE FUNCTION get_status_label(status_code integer)
RETURNS text AS $$
BEGIN
  RETURN CASE status_code
    WHEN 1 THEN 'Active'
    WHEN 2 THEN 'Inactive'
    WHEN 3 THEN 'Pending'
    ELSE
      RAISE EXCEPTION 'Invalid status code: %', status_code
        USING HINT = 'Valid codes are 1, 2, 3';
  END;
END;
$$ LANGUAGE plpgsql;

Choose the approach based on whether unexpected values should be handled gracefully or treated as errors.

3Handle NULL values explicitly

If the CASE expression or WHEN conditions might involve NULL values, handle them explicitly:

sql

-- Problem: NULL values won't match WHEN NULL
CREATE OR REPLACE FUNCTION categorize_value(val integer)
RETURNS text AS $$
BEGIN
  RETURN CASE val
    WHEN 1 THEN 'Low'
    WHEN 2 THEN 'Medium'
    WHEN 3 THEN 'High'
    -- WHEN NULL THEN 'Unknown' -- This won't work as expected
    ELSE 'Unexpected'  -- NULL will fall here, not cause case_not_found
  END;
END;
$$ LANGUAGE plpgsql;

-- Better: Use searched CASE for NULL handling
CREATE OR REPLACE FUNCTION categorize_value(val integer)
RETURNS text AS $$
BEGIN
  RETURN CASE
    WHEN val IS NULL THEN 'Unknown'
    WHEN val = 1 THEN 'Low'
    WHEN val = 2 THEN 'Medium'
    WHEN val = 3 THEN 'High'
    ELSE 'Out of range'
  END;
END;
$$ LANGUAGE plpgsql;

Remember: WHEN NULL in a simple CASE won't match NULL values because NULL = NULL is false in SQL.

4Validate input parameters before CASE

For critical functions, validate inputs before the CASE statement:

sql

CREATE OR REPLACE FUNCTION process_order_status(status text)
RETURNS text AS $$
BEGIN
  -- Validate input first
  IF status NOT IN ('new', 'processing', 'shipped', 'delivered', 'cancelled') THEN
    RAISE EXCEPTION 'Invalid order status: "%". Valid values: new, processing, shipped, delivered, cancelled', status;
  END IF;

  RETURN CASE status
    WHEN 'new' THEN 'Order received'
    WHEN 'processing' THEN 'In progress'
    WHEN 'shipped' THEN 'Shipped to customer'
    WHEN 'delivered' THEN 'Delivery confirmed'
    WHEN 'cancelled' THEN 'Order cancelled'
    -- No ELSE needed since we validated above
  END;
END;
$$ LANGUAGE plpgsql;

This approach provides better error messages and separates validation from business logic.

5Test with comprehensive test cases

Create test cases to verify all possible inputs are handled:

sql

-- Test the fixed function
SELECT get_status_label(1) = 'Active' AS test1;
SELECT get_status_label(2) = 'Inactive' AS test2;
SELECT get_status_label(3) = 'Pending' AS test3;
SELECT get_status_label(4) = 'Unknown' AS test4;  -- Previously would fail
SELECT get_status_label(NULL) = 'Unknown' AS test5;

-- For functions that should raise errors on invalid input:
DO $$
BEGIN
  BEGIN
    PERFORM get_status_label(999);  -- Should raise exception
    RAISE EXCEPTION 'Expected exception not raised';
  EXCEPTION
    WHEN OTHERS THEN
      -- Expected behavior
      RAISE NOTICE 'Correctly raised exception: %', SQLERRM;
  END;
END $$;

-- Check existing data for uncovered values
SELECT DISTINCT status_code 
FROM orders 
WHERE status_code NOT IN (1, 2, 3);  -- Find values needing handling

Advanced notes

The "case_not_found" error is specific to PL/pgSQL and does not occur in regular SQL CASE expressions. In standard SQL, a CASE expression without ELSE returns NULL when no WHEN condition matches. However, in PL/pgSQL, this is treated as an error to help catch programming mistakes. This behavior difference is important when migrating logic between SQL and PL/pgSQL contexts. For complex conditional logic, consider using lookup tables or enums instead of hardcoded CASE statements, as they are easier to maintain and extend. Also note that the error code 20000 is part of PostgreSQL's "PL/pgSQL Error Codes" category (class 20).

Frequently asked questions

What does "20000: case_not_found" mean?: In PostgreSQL's PL/pgSQL language, CASE statements are used for conditional logic similar to switch statements in other programming languages. The error "20000: case_not_found" is raised when a CASE expression evaluates but none of the WHEN conditions match, and there is no ELSE clause to provide a default value. This is a runtime error that causes the current function or procedure to stop execution with an exception.
How do I fix "20000: case_not_found"?: The PostgreSQL error "20000: case_not_found" occurs in PL/pgSQL when a CASE statement executes without matching any WHEN condition and no ELSE clause is provided. This runtime error indicates that the CASE expression failed to find a matching branch, causing the function or procedure to abort.
What causes "20000: case_not_found"?: CASE statement without an ELSE clause encountering unexpected input values Data changes that introduce new values not covered by existing WHEN conditions Boundary conditions or edge cases not accounted for in CASE logic
Is the "20000: case_not_found" error serious?: It is a medium-severity error — more of a blocker than a risk. The steps above resolve it.

Official resources & further reading

📚PostgreSQL Official Documentation
📖
postgresql.org - plpgsql control structures(Official Docs)
📖
postgresql.org - errcodes appendix(Official Docs)
📖
postgresql.org - plpgsql errors and messages(Official Docs)
💬
stackoverflow.com - postgresql+plpgsql+case(Stack Overflow)