PostgreSQL Case

The Searched CASE Form

CASE evaluates a list of WHEN conditions in order and returns the result tied to the first one that is true. If none match, it falls back to the value in ELSE, or to NULL if ELSE is omitted. The whole expression is treated as a single value, so it can be used anywhere a column or literal could appear.

SELECT order_id, total_amount,
       CASE
         WHEN total_amount >= 500 THEN 'large'
         WHEN total_amount >= 100 THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders;

Conditions are checked top to bottom, and evaluation stops at the first match — so order matters. Here, a total_amount of 500 matches the first WHEN and is labeled 'large' before the 'medium' branch is ever considered.

The Simple CASE Form

When every branch compares the same single expression against different exact values, the simple form is more compact: CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END.

SELECT order_id,
       CASE status
         WHEN 'P' THEN 'Pending'
         WHEN 'S' THEN 'Shipped'
         WHEN 'C' THEN 'Cancelled'
         ELSE 'Unknown'
       END AS status_label
FROM orders;

CASE Inside Aggregates

A very common pattern is placing CASE inside an aggregate function to build a pivot-style report, turning row values into separate summed columns without a subquery.

SELECT department,
       COUNT(*) AS total_employees,
       COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_earners,
       SUM(CASE WHEN is_manager THEN 1 ELSE 0 END) AS manager_count
FROM employees
GROUP BY department;

COUNT ignores NULL values, so COUNT(CASE WHEN salary > 80000 THEN 1 END) only counts rows where the condition is true, since non-matching rows implicitly produce NULL and get skipped.

• Searched CASE: CASE WHEN condition THEN result ... ELSE default END
• Simple CASE: CASE expr WHEN value THEN result ... ELSE default END
• Branches are evaluated in order, and the first true match wins
• Omitting ELSE yields NULL when no branch matches
• All THEN and ELSE results should share a compatible data type