Chapter 43: PostgreSQL CASE Expression
The CASE expression.
Many beginners see CASE and think:
“This is just like if-else in programming, right? Why bother when I can do it in application code?”
And the honest answer is:
Yes, it is exactly like if-elseif-else — but doing it inside the database is often 10× cleaner, 10× faster, and 10× more maintainable than sending raw data to the application and doing logic there.
CASE is not a statement (it doesn’t change data). It is an expression — it returns a value that you can use anywhere an expression is allowed: in SELECT, WHERE, ORDER BY, GROUP BY, HAVING, UPDATE SET, INSERT VALUES, etc.
PostgreSQL offers two syntax forms of CASE — both are important.
1. Two forms of CASE – understand both
Form 1: Simple CASE (most common – like switch/case)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CASE column_or_expression WHEN value1 THEN result1 WHEN value2 THEN result2 ... [ELSE else_result] END |
Form 2: Searched CASE (more flexible – like if-elseif-else)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ... [ELSE else_result] END |
- The first WHEN that is true wins
- If none match → ELSE (or NULL if no ELSE)
- Can be nested (CASE inside CASE) — but don’t overdo it
2. Real, practical examples (using our students & bookstore tables)
Example 1: Simple CASE – classify GPA into letter grades
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT first_name || ' ' || last_name AS student, gpa, CASE gpa WHEN 10.00 THEN 'Outstanding' WHEN 9.00 TO 9.99 THEN 'Excellent' -- PostgreSQL allows range here? No — use searched CASE -- Wrong — simple CASE does exact match only END AS performance FROM students; |
Correct (use searched CASE for ranges):
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT first_name || ' ' || last_name AS student, gpa, CASE WHEN gpa = 10.00 THEN 'Outstanding' WHEN gpa >= 9.00 AND gpa < 10.00 THEN 'Excellent' WHEN gpa >= 8.00 AND gpa < 9.00 THEN 'Very Good' WHEN gpa >= 7.00 AND gpa < 8.00 THEN 'Good' WHEN gpa >= 6.00 AND gpa < 7.00 THEN 'Satisfactory' ELSE 'Needs Improvement' END AS grade_category FROM students ORDER BY gpa DESC; |
Result (example):
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
student_name | gpa | grade_category ---------------|-------|----------------- Meera Iyer | 10.00 | Outstanding Priya Reddy | 9.60 | Excellent Sneha Kumar | 9.20 | Excellent Aarav Patel | 8.95 | Very Good Vikram Naik | 8.40 | Very Good Rahul Sharma | 7.85 | Good |
Example 2: CASE in UPDATE (very common real-world use)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
-- Give grace marks to students based on enrollment year UPDATE students SET gpa = CASE WHEN enrollment_year = 2025 THEN gpa + 0.3 WHEN enrollment_year = 2024 THEN gpa + 0.2 ELSE gpa + 0.1 END WHERE gpa < 9.0; |
→ Very clean — no multiple UPDATE statements
Example 3: CASE in ORDER BY (super useful for custom sorting)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT first_name || ' ' || last_name AS student, gpa, enrollment_year FROM students ORDER BY CASE enrollment_year WHEN 2025 THEN 1 WHEN 2024 THEN 2 ELSE 3 END, gpa DESC; |
→ First show 2025 students, then 2024, then older — within each year sort by GPA descending
Example 4: CASE in WHERE (conditional filtering)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
SELECT title, price, stock_quantity FROM books WHERE CASE WHEN price < 300 THEN stock_quantity > 50 WHEN price BETWEEN 300 AND 500 THEN stock_quantity > 20 ELSE stock_quantity > 5 END; |
→ Different stock threshold based on price range
Example 5: CASE with aggregates (inside SELECT)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT city, COUNT(*) AS total_students, SUM(CASE WHEN fees_paid THEN 1 ELSE 0 END) AS paid_students, ROUND(AVG(CASE WHEN fees_paid THEN gpa END), 2) AS avg_gpa_paid, ROUND(AVG(CASE WHEN NOT fees_paid THEN gpa END), 2) AS avg_gpa_unpaid FROM students GROUP BY city HAVING COUNT(*) >= 2; |
→ Very powerful — conditional aggregation without multiple queries
5. Important rules & gotchas (2026 PostgreSQL reality)
| Rule / Mistake | What happens / Surprise | Fix / Best practice |
|---|---|---|
| No ELSE clause | Returns NULL when no WHEN matches | Always add ELSE unless NULL is correct |
| Multiple WHEN true | Only first true WHEN executes | Order matters — put most specific first |
| CASE in WHERE on large table | Can prevent index usage | Prefer WHERE + CASE in SELECT when possible |
| Comparing NULL | WHEN NULL THEN … never matches | Use IS NULL in searched CASE |
| Nested CASE | Allowed — but readability suffers | Keep nesting shallow — use multiple CASE or subqueries |
| Performance | Usually very fast — optimizer understands it well | Avoid very complex CASE inside hot loops |
6. Quick cheat-sheet (keep in your notes)
| Goal | Typical CASE pattern | Alternative (if simpler) |
|---|---|---|
| Classify numeric ranges | CASE WHEN gpa >= 9 THEN ‘Excellent’ … END | — |
| Conditional update | UPDATE … SET col = CASE WHEN … THEN … END | Multiple UPDATE statements (less clean) |
| Custom sort order | ORDER BY CASE status WHEN ‘urgent’ THEN 1 … END | — |
| Conditional count / average | SUM(CASE WHEN paid THEN 1 ELSE 0 END) | FILTER clause (PostgreSQL 9.4+) |
| Replace NULL or default value | COALESCE(col, CASE … END) | COALESCE or NULLIF sometimes simpler |
Your mini homework right now
- Write CASE query: classify students as ‘High Achiever’ (gpa ≥ 9), ‘Good’ (8–8.99), ‘Average’ (<8)
- Add another CASE column: ‘Paid’ or ‘Pending’ based on fees_paid
- Write UPDATE using CASE: add 0.5 grace to students enrolled in 2025 with gpa < 8
- Use CASE in ORDER BY: show 2025 students first, then 2024, sorted by gpa DESC inside each year
Next class?
Tell me:
- Want CASE vs FILTER clause (modern aggregate filter) comparison?
- CASE inside JOIN conditions or WHERE tricky examples?
- How CASE interacts with NULLs + COALESCE / NULLIF?
- Or move to WINDOW functions (ROW_NUMBER, RANK, etc.)?
Your teacher is ready — what’s the next topic? 🚀
