PostgreSQL CASE Expression

PostgreSQL CASE Expression

The CASE expression in PostgreSQL is used to add conditional logic inside SQL queries.
It works like if–else statements in programming languages.

1️⃣ CASE Expression Syntax

Simple CASE

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
ELSE default_result
END

Searched CASE (Most Common)

CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END

2️⃣ Simple CASE Example

SELECT name,
CASE status
WHEN 'active' THEN 'User is Active'
WHEN 'inactive' THEN 'User is Inactive'
ELSE 'Unknown'
END AS user_status
FROM users;

3️⃣ Searched CASE Example

SELECT name, age,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age BETWEEN 18 AND 59 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;

✔ Most widely used form.

4️⃣ CASE in WHERE Clause

SELECT *
FROM users
WHERE
CASE
WHEN role = 'admin' THEN true
ELSE false
END;

✔ Filters rows conditionally.

5️⃣ CASE in ORDER BY

Custom sorting:

SELECT name, role
FROM users
ORDER BY
CASE
WHEN role = 'admin' THEN 1
WHEN role = 'manager' THEN 2
ELSE 3
END;

6️⃣ CASE with Aggregate Functions

SELECT
COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_users,
COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_users
FROM users;

7️⃣ CASE in UPDATE

UPDATE users
SET status =
CASE
WHEN last_login < '2023-01-01' THEN 'inactive'
ELSE 'active'
END;

8️⃣ CASE in JOIN Conditions

SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o
ON u.id =
CASE
WHEN u.role = 'admin' THEN o.admin_id
ELSE o.user_id
END;

9️⃣ Nested CASE

CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 75 THEN 'B'
ELSE
CASE
WHEN score >= 60 THEN 'C'
ELSE 'Fail'
END
END

✔ Avoid deep nesting when possible.

🔟 CASE vs IF

PostgreSQL does not support IF in SQL queries.
✔ Use CASE instead.

1️⃣1️⃣ Common Mistakes

❌ Forgetting END
❌ Mixing data types in THEN results
❌ Missing ELSE (returns NULL)

1️⃣2️⃣ Best Practices

✔ Always include ELSE
✔ Keep CASE logic readable
✔ Prefer searched CASE for complex logic
✔ Avoid CASE in JOIN when possible (performance)

Quick Summary

CASE → conditional logic
Simple CASE → value matching
Searched CASE → condition-based

You may also like...