PostgreSQL CASE WHEN: conditional logic in SQL

Quick answer

SELECT name,
  CASE
    WHEN age >= 65 THEN 'retired'
    WHEN age >= 40 THEN 'senior'
    ELSE 'junior'
  END AS bracket
FROM users;

 name  | age | bracket
-------+-----+---------
 Ada   |  36 | junior
 Alan  |  41 | senior
 Grace |  85 | retired

CASE is an expression, not a statement. It returns a value, so it goes anywhere a value goes: SELECT, WHERE, ORDER BY, GROUP BY, UPDATE ... SET, even inside an aggregate.

Two forms

Searched CASE takes a full condition per branch. This is the one to use:

CASE
  WHEN age >= 65 THEN 'retired'
  WHEN age >= 40 THEN 'senior'
  ELSE 'junior'
END

Simple CASE compares one expression against values:

CASE status
  WHEN 'active'   THEN 'Active user'
  WHEN 'inactive' THEN 'Dormant'
  ELSE 'Unknown'
END

The simple form is shorter for equality checks against a single column, and it has a trap the searched form does not. See below.

First match wins, so order matters

Branches are evaluated top to bottom and evaluation stops at the first true one. That makes ordering load-bearing:

-- Wrong: nobody is ever 'retired'
CASE
  WHEN age >= 40 THEN 'senior'
  WHEN age >= 65 THEN 'retired'
  ELSE 'junior'
END

An 85-year-old matches age >= 40 first and gets senior. The retired branch is unreachable. PostgreSQL will not warn you about this, because the branches are legal and it has no idea what you meant.

Order overlapping conditions from most specific to least.

The NULL trap in simple CASE

CASE x WHEN NULL never matches anything:

SELECT name, status,
  CASE status WHEN NULL THEN 'matched' ELSE 'fell-through' END AS simple_form,
  CASE WHEN status IS NULL THEN 'matched' ELSE 'not-null' END AS searched_form
FROM users;

 name  | status | simple_form  | searched_form
-------+--------+--------------+---------------
 Ada   | active | fell-through | not-null
 Alan  |        | fell-through | matched
 Grace | active | fell-through | not-null

Alan's status is NULL, and the simple form still fell through.

The reason is that simple CASE compares with =, and NULL = NULL is not true, it is unknown. So the branch never fires. The searched form works because IS NULL is the correct way to test for NULL.

This is not a PostgreSQL quirk, it is how NULL works in SQL, but it bites here because CASE status WHEN NULL looks like it should work. If any branch of your CASE needs to test for NULL, use the searched form.

A missing ELSE returns NULL

SELECT CASE WHEN false THEN 'x' END AS no_else;

 no_else
---------

NULL. Not an empty string, not an error.

This is the most common source of surprise NULLs in CASE expressions. If every input should produce a value, write an explicit ELSE, even if it is only ELSE NULL to document the intent.

It matters more than it looks when the result feeds something else. CASE returning NULL into a NOT NULL column, a WHERE clause, or string concatenation propagates the NULL onward, and the eventual failure happens somewhere far from the CASE.

All branches must share a type

SELECT CASE WHEN true THEN 1 ELSE 'text' END;

ERROR:  invalid input syntax for type integer: "text"
LINE 1: SELECT CASE WHEN true THEN 1 ELSE 'text' END;
                                          ^

PostgreSQL resolves a single result type for the whole expression. It found 1 first, decided the type was integer, then failed trying to read 'text' as one.

The error points at the second branch, but the cause is the combination. Cast explicitly when the branches genuinely differ:

SELECT CASE WHEN true THEN 1::text ELSE 'text' END;

This is stricter than MySQL, which would coerce and carry on. Code ported from MySQL hits it regularly.

Conditional aggregates

This is where CASE earns its place, and it is the pattern worth knowing:

SELECT
  count(*)                                              AS total,
  count(*) FILTER (WHERE status = 'active')             AS active,
  sum(CASE WHEN status = 'active' THEN amount ELSE 0 END) AS active_revenue,
  avg(CASE WHEN status = 'active' THEN amount END)      AS active_avg
FROM orders;

Two things to notice.

PostgreSQL has FILTER, which is cleaner than CASE for conditional counts and is standard SQL:

count(*) FILTER (WHERE status = 'active')
sum(amount) FILTER (WHERE status = 'active')

Prefer FILTER where it applies. It says what it means and it is easier to read than sum(CASE WHEN ... THEN 1 ELSE 0 END). MySQL does not have it, which is why CASE inside aggregates is so widespread.

Note the missing ELSE in the avg. That is deliberate. avg ignores NULLs, so omitting ELSE averages only the matching rows. Adding ELSE 0 would feed a zero into the average for every non-matching row and drag it toward zero, which answers a different question and usually not the one you wanted.

Pivoting rows into columns

The classic use:

SELECT
  date_trunc('month', created_at) AS month,
  count(*) FILTER (WHERE status = 'active')   AS active,
  count(*) FILTER (WHERE status = 'inactive') AS inactive,
  count(*) FILTER (WHERE status IS NULL)      AS unknown
FROM orders
GROUP BY 1
ORDER BY 1;

One row per month, one column per status. This is the readable way to build a summary table without a crosstab extension.

CASE in ORDER BY

Custom sort orders, where the ordering is not alphabetical or numeric:

SELECT * FROM tasks
ORDER BY
  CASE priority
    WHEN 'critical' THEN 1
    WHEN 'high'     THEN 2
    WHEN 'normal'   THEN 3
    ELSE 4
  END,
  created_at DESC;

Useful, and worth knowing the cost: CASE in ORDER BY cannot use an index, so it forces a sort. Fine for a few thousand rows, not for a paginated query over millions.

If you sort this way often, the honest fix is a priority_rank column, or an enum type, which sorts by declaration order for free:

CREATE TYPE priority AS ENUM ('critical', 'high', 'normal', 'low');
-- ORDER BY priority now sorts correctly, and can use an index

Pushing NULLs to the end is a common variant, though PostgreSQL has dedicated syntax that is better:

ORDER BY last_login DESC NULLS LAST      -- prefer this
ORDER BY CASE WHEN last_login IS NULL THEN 1 ELSE 0 END, last_login DESC

CASE in UPDATE

Updating many rows to different values in one statement:

UPDATE products
SET price = CASE
  WHEN category = 'clearance' THEN price * 0.5
  WHEN category = 'sale'      THEN price * 0.8
  ELSE price
END;

The ELSE price is doing real work there. Without it, every row outside those two categories gets NULL, because a missing ELSE returns NULL. That is a genuinely destructive typo, and it will not error:

-- Three products, all priced 100. Note the missing ELSE.
UPDATE products SET price = CASE
  WHEN category = 'clearance' THEN price * 0.5
  WHEN category = 'sale'      THEN price * 0.8
END;

UPDATE 3

 category  | price
-----------+-------
 clearance |  50.0
 regular   |
 sale      |  80.0

regular had a price of 100 and now has none. UPDATE 3 is the tell: the statement touched every row, not just the two you meant. There is no error and no warning, and if price were NOT NULL you would at least get a constraint failure. If it is nullable, you get silent data loss.

Better still, do not touch rows you are not changing:

UPDATE products
SET price = CASE
  WHEN category = 'clearance' THEN price * 0.5
  WHEN category = 'sale'      THEN price * 0.8
END
WHERE category IN ('clearance', 'sale');

Now the WHERE restricts the rows, the CASE picks the value, and no ELSE is needed because no other rows are touched. Fewer rows written, less bloat, and no way to null out a column by accident.

CASE in WHERE, and why not to

Legal:

SELECT * FROM users
WHERE CASE WHEN age > 40 THEN status = 'active' ELSE status IS NOT NULL END;

Avoid it. Wrapping columns in an expression prevents index use, and plain boolean logic says the same thing:

SELECT * FROM users
WHERE (age > 40 AND status = 'active')
   OR (age <= 40 AND status IS NOT NULL);

Short-circuiting is not guaranteed

CASE is documented to stop at the first true branch, and generally does. But PostgreSQL may evaluate constant subexpressions during planning, before any branch is chosen. So this is not a reliable guard:

-- Does NOT reliably prevent the division
SELECT CASE WHEN divisor <> 0 THEN numerator / divisor ELSE NULL END FROM t;

In practice that specific example works, because divisor is a column and cannot be folded at plan time. But do not rely on CASE to suppress errors in general. For division specifically, NULLIF is the idiom:

SELECT numerator / NULLIF(divisor, 0) FROM t;

Common problems

CASE returns NULL unexpectedly. No branch matched and there is no ELSE.

A branch never fires. An earlier branch matches first. Reorder from specific to general.

CASE x WHEN NULL does not match. It compares with =. Use the searched form and IS NULL.

invalid input syntax for type .... Branches return different types. Cast explicitly.

My UPDATE nulled out a column. A CASE without ELSE in SET. Add ELSE col, or restrict with WHERE.

The query got slow. CASE in WHERE or ORDER BY prevents index use.

Quick reference