CASE Expressions in PostgreSQL

CASE is SQL's equivalent of an if/else chain — it evaluates conditions in order and returns the value tied to the first one that's true.

Searched CASE (Most Common Form)

SELECT name, salary,
  CASE
    WHEN salary < 40000 THEN 'Junior'
    WHEN salary < 80000 THEN 'Mid'
    ELSE 'Senior'
  END AS salary_band
FROM employees;

Conditions are checked top to bottom — the first match wins, and ELSE catches everything else (it's optional; without it, unmatched rows get NULL).

Simple CASE — Comparing One Value

SELECT name,
  CASE department
    WHEN 'ENG' THEN 'Engineering'
    WHEN 'HR' THEN 'Human Resources'
    ELSE department
  END AS department_full
FROM employees;

CASE Inside Aggregates — Conditional Counting

A very common pattern: turning a condition into a count without a separate query per condition.

SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN gender = 'F' THEN 1 END) AS female_count,
  COUNT(CASE WHEN salary > 60000 THEN 1 END) AS high_earners
FROM employees;

COUNT() ignores NULL, so a CASE with no ELSE (returning NULL for non-matches) works perfectly here — only matching rows get counted.

CASE in ORDER BY

SELECT name, department
FROM employees
ORDER BY
  CASE WHEN department = 'Engineering' THEN 0 ELSE 1 END,
  name;
-- Engineering employees first, then everyone else, alphabetically within each group

Common Errors & Troubleshooting

• ERROR: CASE types text and integer cannot be matched — every WHEN ... THEN branch (and the ELSE) must return the same (or compatible) data type; mixing a text result in one branch with a number in another causes this error.
• Forgetting ELSE and getting unexpected NULLs — without an ELSE, any row that doesn't match any WHEN condition returns NULL, which can silently propagate into further calculations.
• COUNT(CASE WHEN ... THEN 1 END) returning 0 instead of skipping — this pattern relies on COUNT() ignoring NULL; if you accidentally write THEN 0 instead of leaving the implicit NULL (no ELSE), every row gets counted, defeating the purpose.

Key Takeaways

• CASE evaluates conditions top-to-bottom and returns the first match — order your conditions from most to least specific.
• COUNT(CASE WHEN condition THEN 1 END) is the standard pattern for conditional counting inside one aggregate query.
• CASE works anywhere an expression is valid — SELECT, WHERE, ORDER BY, and even inside GROUP BY.