How to Use CASE WHEN in PostgreSQL

What does CASE WHEN do in PostgreSQL?

CASE WHEN lets you return different values depending on one or more boolean tests. PostgreSQL evaluates the WHEN clauses in order and outputs the first matching THEN value; otherwise, it falls back to ELSE.

When should I use CASE WHEN?

Use CASE WHEN to build derived columns, to label rows, to create bucketing logic, or to apply conditional ORDER BY or GROUP BY rules without writing multiple queries.

What is the exact syntax of CASE WHEN?

CASE
WHEN <condition1> THEN <result1>
[WHEN <condition2> THEN <result2> ...]
[ELSE <default_result>]
END

How do I create a conditional column?

SELECT
user_id,
CASE WHEN last_login > NOW() - INTERVAL '30 days' THEN 'active'
ELSE 'inactive' END AS status
FROM users;

The query labels users as active if they logged in within the last 30 days, otherwise inactive.

How can I nest CASE WHEN statements?

SELECT
revenue,
CASE
WHEN revenue >= 100000 THEN 'platinum'
WHEN revenue >= 50000 THEN 'gold'
WHEN revenue >= 10000 THEN 'silver'
ELSE 'bronze'
END AS tier
FROM accounts;

Nested or multi-branch CASE WHENs let you build tiering logic clearly in one pass.

How do I use CASE WHEN in ORDER BY?

SELECT *
FROM tickets
ORDER BY
CASE WHEN priority = 'critical' THEN 1
WHEN priority = 'high' THEN 2
ELSE 3 END,
created_at;

Critical tickets float to the top without changing any stored data.

Best practices for CASE WHEN

Keep conditions mutually exclusive to avoid confusion. Add ELSE for clarity, even if it simply returns NULL. Use indentation that lines up WHEN and THEN for readability. Test with small datasets before running in production.

Common mistakes and how to avoid them

Boolean expressions inside CASE WHEN must not include trailing semicolons. Remember that CASE WHEN stops at the first TRUE match; order your conditions from most specific to least specific.

FAQ about CASE WHEN

Can I use subqueries inside THEN?

Yes. Any valid expression—including SELECT statements, function calls, or arithmetic—can appear after THEN, provided it returns a single value per row.

Is CASE WHEN slower than multiple queries?

Generally no. PostgreSQL evaluates CASE WHEN per row in memory. It is almost always faster than UNION-based alternatives that scan data multiple times.