PostgreSQL 18 reference · Conditional and Type Expressions
PostgreSQL CASE Expression
Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END, usable anywhere an expression is.
PostgreSQL 18
Syntax
CASE WHEN condition THEN result [WHEN ...] [ELSE result] END | CASE expr WHEN value THEN result ... END
Parameters
| Parameter | Description |
|---|---|
condition / value |
Tested in order; the first match wins. |
ELSE optional |
The result when nothing matches; NULL if omitted. |
Return value
The result of the first matching branch.
Examples
SELECT name, salary,
CASE WHEN salary >= 200000 THEN 'executive'
WHEN salary >= 120000 THEN 'senior'
ELSE 'staff' END AS band
FROM employees
WHERE employee_id IN (1, 4, 9);
Output
name | salary | band -------------+--------+----------- Sofia Reyes | 250000 | executive Jonas Berg | 140000 | senior Emma Novak | 88000 | staff (3 rows)
Run this on your computer — see “Get Started” for setup.
SELECT count(*) FILTER (WHERE status = 'delivered') AS delivered,
sum(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending
FROM orders;
Output
delivered | pending
-----------+---------
7 | 2
(1 row)Run this on your computer — see “Get Started” for setup.
In other languages
Related
COALESCE()· Return the first argument that isn't NULL. The standard way to replace NULL with a default.NULLIF()· Return NULL if two values are equal, otherwise the first one. Commonly used to avoid division by zero.FILTER· Restrict which rows one aggregate sees with FILTER (WHERE …), so one query can count several conditions.- SQL CASE · tutorial