PostgreSQL CASE WHEN: Syntax, Examples and Gotchas

TL;DR: PostgreSQL CASE is a conditional expression: CASE WHEN condition THEN result [WHEN ...] [ELSE result] END. It returns the result of the first WHEN that is true, falls back to ELSE, and returns NULL if there is no ELSE and nothing matched. You can use it anywhere an expression is allowed: SELECT, WHERE, ORDER BY, GROUP BY, UPDATE ... SET and inside aggregates.

CASE in PostgreSQL is the SQL-standard way to write if/else logic inside a query. SELECT CASE WHEN amount >= 1000 THEN 'large' WHEN amount >= 100 THEN 'medium' ELSE 'small' END AS size FROM orders; labels every row by checking the WHEN conditions top to bottom and stopping at the first true one. PostgreSQL 18 (the current release in the official documentation) supports two forms, "searched" CASE and "simple" CASE, and both are expressions, not statements, so they return a value. This page covers both forms, the CASE WHEN patterns people actually need (multiple conditions, NULLs, pivots, conditional updates, custom sort order), and the three behaviors that break CASE in production: type mismatch errors, plan-time evaluation of "unreachable" arms, and aggregates that run before CASE can guard them.

What is the syntax of CASE WHEN in PostgreSQL?

The searched CASE form is the general one. Each condition is any boolean expression, and the results can be columns, literals or other expressions:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  [ ... ]
  [ ELSE default_result ]
END

The rules, straight from the PostgreSQL manual (Section 9.18.1):

Rule | What happens

Evaluation order | WHEN clauses are checked top to bottom; the first true one wins and the rest are skipped

No match, ELSE present | ELSE result is returned

No match, no ELSE | NULL is returned (no error in plain SQL)

Result types | All THEN/ELSE results must convert to one common type

Where allowed | Anywhere an expression is valid

Because the first true WHEN wins, order your conditions from most specific to least specific. In the example below, swapping the first two lines would label every order of 1,000 or more as medium:

SELECT id,
       amount,
       CASE
         WHEN amount >= 1000 THEN 'large'
         WHEN amount >= 100  THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders;

Give the expression an alias (AS order_size); without one, PostgreSQL names the output column case.

What is the difference between simple CASE and searched CASE?

Simple CASE compares one expression against a list of values with equality, like a switch statement in C. Searched CASE evaluates independent boolean conditions. These two queries return the same thing:

 Simple CASE
SELECT id,
       CASE status
         WHEN 'trialing' THEN 'Trial'
         WHEN 'active'   THEN 'Paying'
         WHEN 'canceled' THEN 'Churned'
         ELSE 'Other'
       END AS lifecycle
FROM subscriptions;

 Searched CASE
SELECT id,
       CASE
         WHEN status = 'trialing' THEN 'Trial'
         WHEN status = 'active'   THEN 'Paying'
         WHEN status = 'canceled' THEN 'Churned'
         ELSE 'Other'
       END AS lifecycle
FROM subscriptions;

Use simple CASE for a straight value-to-label mapping. Use searched CASE for ranges (>=, BETWEEN), multiple columns, LIKE, IN lists or NULL checks. The NULL case is the classic trap: CASE status WHEN NULL THEN 'missing' END never matches, because simple CASE tests status = NULL, which is NULL rather than true. Write CASE WHEN status IS NULL THEN 'missing' END instead.

How do you write CASE with multiple conditions in PostgreSQL?

Combine conditions inside one WHEN with AND, OR and parentheses, or list values with IN:

SELECT id,
       CASE
         WHEN plan IN ('pro', 'business') AND mrr_cents >= 50000 THEN 'key account'
         WHEN plan IN ('pro', 'business')                         THEN 'paid'
         WHEN trial_ends_at > now() OR plan = 'free'               THEN 'pipeline'
         ELSE 'inactive'
       END AS segment
FROM accounts;

You can also nest a CASE inside a THEN, but two levels is usually the limit before a flat list of WHENs with combined conditions is easier to read and review.

How do you use CASE in WHERE, ORDER BY and GROUP BY?

CASE works in every clause that accepts an expression.

Custom sort order, where alphabetical order would be wrong:

SELECT id, priority
FROM tickets
ORDER BY CASE priority
           WHEN 'urgent' THEN 1
           WHEN 'high'   THEN 2
           WHEN 'normal' THEN 3
           ELSE 4
         END,
         created_at;

Grouping into buckets, then counting each bucket:

SELECT CASE
         WHEN amount < 100  THEN '0-99'
         WHEN amount < 1000 THEN '100-999'
         ELSE '1000+'
       END AS bucket,
       count(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

GROUP BY 1 refers to the first select-list column, so you don't have to repeat the CASE.

In WHERE, CASE is mostly useful to guard a risky computation. The PostgreSQL manual's own example avoids division by zero:

SELECT * FROM metrics
WHERE CASE WHEN visits <> 0 THEN signups::numeric / visits > 0.05 ELSE false END;

The manual also notes that a CASE used this way "will defeat optimization attempts", and that rewriting the condition (here signups > 0.05 * visits) is better when possible.

How do you use CASE inside COUNT, SUM and other aggregates?

Wrapping CASE in an aggregate is the standard way to pivot rows into columns, for example signups per plan per month:

SELECT date_trunc('month', created_at) AS month,
       SUM(CASE WHEN plan = 'free' THEN 1 ELSE 0 END) AS free_signups,
       SUM(CASE WHEN plan = 'pro'  THEN 1 ELSE 0 END) AS pro_signups,
       COUNT(CASE WHEN country = 'IN' THEN 1 END)     AS india_signups
FROM users
GROUP BY 1
ORDER BY 1;

COUNT(CASE WHEN ... THEN 1 END) works because the missing ELSE yields NULL and count(expression) skips NULLs.

Since PostgreSQL 9.4 there is a cleaner standard alternative, the FILTER clause:

SELECT date_trunc('month', created_at) AS month,
       count(*) FILTER (WHERE plan = 'free')    AS free_signups,
       count(*) FILTER (WHERE plan = 'pro')     AS pro_signups,
       count(*) FILTER (WHERE country = 'IN')   AS india_signups
FROM users
GROUP BY 1
ORDER BY 1;

Prefer FILTER in PostgreSQL code: it states the intent directly and works with any aggregate, including sum, avg and array_agg. Keep the CASE version when the same SQL must also run on MySQL or SQL Server, which do not support FILTER.

How do you use CASE in an UPDATE statement?

CASE in UPDATE ... SET changes different rows to different values in one pass:

UPDATE accounts
SET tier = CASE
             WHEN mrr_cents >= 100000 THEN 'enterprise'
             WHEN mrr_cents >= 10000  THEN 'growth'
             ELSE 'starter'
           END
WHERE tier IS DISTINCT FROM CASE
             WHEN mrr_cents >= 100000 THEN 'enterprise'
             WHEN mrr_cents >= 10000  THEN 'growth'
             ELSE 'starter'
           END;

The WHERE clause skips rows that already have the right value, which avoids writing a new row version for every row in the table. Remember that without an ELSE, rows that match no WHEN are set to NULL.

Why does PostgreSQL say "CASE types text and integer cannot be matched"?

This error (SQLSTATE 42804, datatype_mismatch) means the THEN and ELSE results belong to different type categories, for example a number in one arm and a string in another:

 Fails: ERROR: CASE types text and integer cannot be matched
SELECT CASE WHEN qty > 0 THEN qty ELSE 'out of stock' END FROM items;

 Works: make every arm the same type
SELECT CASE WHEN qty > 0 THEN qty::text ELSE 'out of stock' END FROM items;

PostgreSQL resolves CASE result types with the same algorithm as UNION (manual Section 10.5). Two details explain the less obvious failures. First, CASE treats its ELSE clause as the "first" input when picking the candidate type, then considers the THEN clauses. Second, untyped string literals are "unknown" and are ignored if another arm has a real type, so THEN 0 ELSE '5' works (the '5' becomes an integer) while THEN 0 ELSE 'none' fails with invalid input syntax for type integer (SQLSTATE 22P02). If all arms are unknown literals, the result is text. Explicit casts on every arm remove the guesswork.

Does PostgreSQL CASE short-circuit?

Mostly, with two documented exceptions. The manual says CASE "does not evaluate any subexpressions that are not needed to determine the result", which is why it is the recommended tool for forcing evaluation order. But Section 4.2.14 lists where that breaks:

When should you use COALESCE, NULLIF or GREATEST instead of CASE?

These are shorthand for common CASE patterns, documented in the same manual section (9.18):

Goal | CASE version | Shorter built-in

Default for NULL | CASE WHEN a IS NULL THEN 0 ELSE a END | COALESCE(a, 0)

Turn a sentinel into NULL | CASE WHEN a = '' THEN NULL ELSE a END | NULLIF(a, '')

Safe division | CASE WHEN b = 0 THEN NULL ELSE a / b END | a / NULLIF(b, 0)

Larger of two values | CASE WHEN a > b THEN a ELSE b END | GREATEST(a, b)

Conditional count | COUNT(CASE WHEN c THEN 1 END) | count(*) FILTER (WHERE c)

One difference to know: GREATEST and LEAST ignore NULL arguments in PostgreSQL (a deviation from the SQL standard), while the CASE version returns b whenever a is NULL because NULL > b is not true.

Is the PL/pgSQL CASE statement the same as the CASE expression?

No. Inside a PL/pgSQL function, CASE ... WHEN ... THEN statements ... END CASE is a control-flow statement that runs commands instead of returning a value. The key behavioral difference: when no WHEN matches and there is no ELSE, the SQL expression returns NULL, but the PL/pgSQL statement raises a CASE_NOT_FOUND exception (SQLSTATE 20000). Always add an ELSE (even ELSE NULL;) to PL/pgSQL CASE statements that might not cover every value.

Skipping the CASE entirely

Most CASE expressions in reporting queries exist to bucket customers, pivot a metric by plan or label statuses for a dashboard. If you'd rather ask "how many signups per plan per month?" and get the table, AI for Database connects to PostgreSQL, writes the SQL (CASE or FILTER included), runs it and shows you the query it used. For related PostgreSQL recipes, see DATEDIFF in PostgreSQL, DATEADD in PostgreSQL, churn analysis on Postgres and cohort analysis from your database.