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.
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.
First, locate the PL/pgSQL function or procedure that's failing. Look for CASE statements without ELSE clauses:
-- 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_foundCheck PostgreSQL logs or use SHOW ERRORS to find the exact function name.
The most straightforward fix is to add an ELSE clause that provides a default value or raises a more informative error:
-- 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.
If the CASE expression or WHEN conditions might involve NULL values, handle them explicitly:
-- 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.
For critical functions, validate inputs before the CASE statement:
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.
Create test cases to verify all possible inputs are handled:
-- 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 handlingThe "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).
ERROR: syntax error at end of input
Syntax error at end of input in PostgreSQL
Bind message supplies N parameters but prepared statement requires M
Bind message supplies N parameters but prepared statement requires M in PostgreSQL
Multidimensional arrays must have sub-arrays with matching dimensions
Multidimensional arrays must have sub-arrays with matching dimensions
ERROR: value too long for type character varying
Value too long for type character varying
insufficient columns in unique constraint for partition key
How to fix "insufficient columns in unique constraint for partition key" in PostgreSQL