Postgres by Example: CASE

CASE is SQL's conditional expression — the closest thing to an if/else. It has two forms. The searched CASE (CASE WHEN cond THEN ... END) evaluates arbitrary boolean conditions. The simple CASE (CASE expr WHEN value THEN ... END) compares an expression to specific values. CASE can be used anywhere an expression is allowed — in SELECT, WHERE, ORDER BY, GROUP BY, and in expressions inside aggregates.

What you'll learn:

• Searched CASE for general conditions
• Simple CASE for value equality
• ELSE and what happens without it
• Using CASE to bucket rows for grouping
• The role of CASE in conditional aggregation (and when FILTER replaces it)

-- Searched CASE (most flexible)
SELECT id, name,
  CASE
    WHEN id <= 2  THEN 'low'
    WHEN id <= 4  THEN 'mid'
    ELSE              'high'
  END AS tier
FROM fruits
ORDER BY id
LIMIT 5;

-- Simple CASE (equality)
SELECT name,
  CASE name
    WHEN 'apricot' THEN 'A'
    WHEN 'blueberry' THEN 'B'
    ELSE '?'
  END AS code
FROM fruits
ORDER BY id
LIMIT 4;

-- CASE in WHERE
SELECT id, name
FROM fruits
WHERE CASE WHEN id < 5 THEN id > 1 ELSE false END
ORDER BY id;

-- CASE for bucketing and counting per bucket
SELECT
  CASE
    WHEN id <= 2 THEN 'low'
    WHEN id <= 4 THEN 'mid'
    ELSE              'high'
  END AS tier,
  count(*) AS cnt
FROM fruits
GROUP BY tier
ORDER BY tier;

-- Conditional aggregation: CASE inside an aggregate vs. FILTER (cleaner)
SELECT
  sum(CASE WHEN total > 30 THEN total ELSE 0 END) AS big_sum_old_style,
  sum(total) FILTER (WHERE total > 30)            AS big_sum_filter
FROM orders_example;

The searched form is the workhorse. PostgreSQL evaluates conditions top-to-bottom and returns the result of the first true one. If no condition matches and there is no ELSE, the result is NULL — sometimes useful, sometimes a bug.

The simple form is shorthand for equality: CASE name WHEN 'a' THEN ... END is the same as CASE WHEN name = 'a' THEN ... END. It is concise when you have many discrete values to map.

Conditional aggregation — counting or summing only matching rows — used to require sum(CASE WHEN cond THEN val ELSE 0 END). The modern equivalent, sum(val) FILTER (WHERE cond), is clearer and faster (no CASE evaluation per row). Use FILTER when you can.

To run:

$ psql -f source/case.sql postgres
 id |    name    | tier
----+------------+------
  1 | apricot    | low
  2 | blueberry  | low
  3 | chestnut   | mid
  4 | damson     | mid
  5 | elderberry | high
(5 rows)
 ...

Common pitfalls:

• All THEN/ELSE branches must agree on a common type — CASE WHEN ... THEN 1 ELSE 'x' END is an error. Cast explicitly when mixing types: CASE WHEN ... THEN 1::text ELSE 'x' END.
• Forgetting END is the most common syntax error.
• Without ELSE, the result is NULL when no branch matches. If that is surprising, add an explicit ELSE ....
• CASE in WHERE works but is usually unnecessary — most conditions are clearer as plain boolean expressions.

Tip: CASE and COALESCE overlap but are not the same. COALESCE(a, b, c) is shorter for "first non-null"; CASE is the right choice when conditions are not just "is null."

Try it: Use CASE to map id to a label like 'small' / 'medium' / 'large' and count per label. Use CASE in ORDER BY to sort priorities in a custom order: ORDER BY CASE priority WHEN 'high' THEN 1 WHEN 'mid' THEN 2 ELSE 3 END. Then rewrite a sum(CASE WHEN ...) as sum(...) FILTER (WHERE ...).

Source: case.sql

Next: CREATE INDEX

Home: Postgres by Example