PostgreSQL CASE Expressions

CASE adds decision-making to a query. It works like a traffic light: if the light is red, stop; if it is green, go; otherwise, slow down. The database checks each row against your conditions and returns the first matching answer.

  Row value
     |
     v
  Condition 1? --yes--> Result 1
     | no
  Condition 2? --yes--> Result 2
     | no
  ELSE ----------------> Default result

Programmers know the same idea as if-then-else. CASE is the SQL version, and it works inside SELECT, WHERE, ORDER BY, and UPDATE.

Forms of CASE

CASE comes in two forms. Both end with the same ELSE and END keywords.

Searched CASE

The searched form tests any condition. Each WHEN holds a true-or-false test.

SELECT student_name, marks,
  CASE
    WHEN marks >= 90 THEN 'Excellent'
    WHEN marks >= 75 THEN 'Good'
    WHEN marks >= 50 THEN 'Pass'
    ELSE 'Retry'
  END AS result
FROM exam_results;

student_name	marks	result
Asha	93	Excellent
Ravi	78	Good
Mina	41	Retry

PostgreSQL reads the WHEN lines from top to bottom and stops at the first match. Asha scores 93, so she receives Excellent and the later lines never run. Place the strictest condition first.

Simple CASE

The simple form compares one column against fixed values. It reads shorter when you match exact values.

SELECT order_id,
  CASE status
    WHEN 'P' THEN 'Packed'
    WHEN 'S' THEN 'Shipped'
    WHEN 'D' THEN 'Delivered'
    ELSE 'Unknown'
  END AS status_name
FROM orders;

Short codes such as P, S, and D save storage. CASE turns them into friendly words for reports.

The ELSE Part

ELSE catches every row that matches no WHEN. Leave it out, and unmatched rows return NULL. Add ELSE whenever you want a clear default value.

CASE in Real Queries

CASE fits almost any part of a statement. The patterns below solve everyday reporting problems.

CASE for Grouping Ranges

Group customers by age band with CASE inside a subquery or GROUP BY.

SELECT
  CASE
    WHEN age < 18 THEN 'Under 18'
    WHEN age < 40 THEN '18 to 39'
    ELSE '40 and above'
  END AS age_band,
  COUNT(*) AS total
FROM customers
GROUP BY 1;

The GROUP BY 1 part points to the first column in the SELECT list. The query counts how many customers fall into each band.

CASE Inside Aggregates

This trick builds a pivot-style summary. Each CASE picks the rows you want to count or add up.

SELECT
  COUNT(CASE WHEN status = 'Delivered' THEN 1 END) AS delivered,
  COUNT(CASE WHEN status = 'Shipped'   THEN 1 END) AS shipped,
  SUM(CASE WHEN status = 'Delivered' THEN amount ELSE 0 END) AS delivered_sales
FROM orders;

  orders table --> CASE filters rows --> one summary row

COUNT ignores NULL values. A CASE without ELSE returns NULL for non-matching rows, so COUNT counts only the matches.

CASE in UPDATE

Apply different changes to different rows in a single statement.

UPDATE products
SET price = CASE
  WHEN category = 'Books'  THEN price * 1.05
  WHEN category = 'Toys'   THEN price * 1.10
  ELSE price
END;

One command replaces three separate UPDATE statements. The ELSE price line keeps other products unchanged.

CASE in ORDER BY

Sort rows in a custom order that no alphabet can produce.

SELECT ticket_id, priority
FROM tickets
ORDER BY CASE priority
  WHEN 'High'   THEN 1
  WHEN 'Medium' THEN 2
  WHEN 'Low'    THEN 3
END;

Alphabetical order would place High before Low before Medium. CASE assigns a rank number to each label, and the sort follows the numbers.

Handling NULL with CASE

A test such as column = NULL never succeeds. Use IS NULL inside WHEN.

SELECT name,
  CASE WHEN phone IS NULL THEN 'No phone' ELSE phone END AS contact
FROM customers;

PostgreSQL offers COALESCE, a shorter tool for this exact job: COALESCE(phone, 'No phone').

Common Mistakes

• Forgetting the END keyword.
• Mixing result types, such as text in one branch and numbers in another.
• Placing a broad condition above a narrow one, so the narrow one never runs.
• Leaving out ELSE and getting unexpected NULL values.