case not found

Category: Case Not FoundVersions: All Postgres versions

What this means

SQLSTATE 20000 is raised when a PL/pgSQL CASE statement does not find a matching WHEN clause and no ELSE clause is provided. The statement terminates with an error.

Why it happens

1PL/pgSQL CASE statement with no matching WHEN clause and no ELSE
2A searched CASE expression encounters a value not covered by any WHEN

How to reproduce

CASE statement with no ELSE for an unhandled value.

trigger — this will ERROR

DO $
DECLARE v INT := 99;
BEGIN
  CASE v
    WHEN 1 THEN RAISE NOTICE 'one';
    WHEN 2 THEN RAISE NOTICE 'two';
    -- no ELSE, 99 not covered
  END CASE;
END $;

ERROR: case not found HINT: CASE statement is missing ELSE part.

Fix 1: Add an ELSE clause to the CASE statement

When the CASE must handle all possible values.

fix

CASE v
  WHEN 1 THEN RAISE NOTICE 'one';
  WHEN 2 THEN RAISE NOTICE 'two';
  ELSE RAISE NOTICE 'other: %', v;
END CASE;

Why this works

An ELSE clause provides a fallback for any value not matched by a WHEN, preventing the 20000 error.

Fix 2: Raise a meaningful exception in the ELSE clause

When an unmatched value represents a programming error.

fix

ELSE RAISE EXCEPTION 'Unexpected value: %', v USING ERRCODE = 'P0001';

Why this works

Raising explicitly in ELSE gives a descriptive error rather than the generic 20000.

Sources

📚 Official docs: https://www.postgresql.org/docs/current/errcodes-appendix.html

📚 Feature docs: https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-CONDITIONALS

🔧 Source ref: Class 20 — Case Not Found

Confidence assessment

✅ HIGH confidence

Standard SQLSTATE. Behaviour well-documented and stable across all Postgres versions.