CASE in PostgreSQL 🚀

The CASE statement in PostgreSQL is used for conditional logic inside SELECT, UPDATE, INSERT, or ORDER BY clauses. It works like an IF-ELSE statement, returning different values based on conditions.

1. Basic Syntax

SELECT column1,       CASE           WHEN condition1 THEN result1           WHEN condition2 THEN result2           ELSE default_result       END AS new_columnFROM table_name;

• WHEN → Defines conditions.
• THEN → Specifies the result if the condition is true.
• ELSE → Provides a default result if none of the conditions match.

2. Example: Categorizing Employees by Salary

SELECT name, salary,       CASE           WHEN salary > 70000 THEN 'High'           WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'           ELSE 'Low'       END AS salary_categoryFROM employees;

🔹 Categorizes employees as High, Medium, or Low based on salary.

3. Example: Assigning Pass/Fail Based on Marks

SELECT student_name, marks,       CASE           WHEN marks >= 50 THEN 'Pass'           ELSE 'Fail'       END AS resultFROM students;

🔹 Marks ≥ 50 → Pass, otherwise Fail.

4. Example: Using CASE in UPDATE

UPDATE employeesSET bonus =     CASE         WHEN experience > 10 THEN salary * 0.20        WHEN experience BETWEEN 5 AND 10 THEN salary * 0.10        ELSE salary * 0.05    END;

🔹 Assigns bonuses based on experience.

5. Example: Using CASE in ORDER BY

SELECT name, roleFROM employeesORDER BY     CASE         WHEN role = 'Manager' THEN 1        WHEN role = 'Engineer' THEN 2        ELSE 3    END;

🔹 Sorts employees so Managers appear first, then Engineers.

6. Example: Using CASE with GROUP BY

SELECT     CASE         WHEN age < 18 THEN 'Minor'        WHEN age BETWEEN 18 AND 60 THEN 'Adult'        ELSE 'Senior'    END AS age_group,    COUNT(*) AS totalFROM peopleGROUP BY age_group;

🔹 Groups people into Minor, Adult, or Senior categories and counts them.

7. Example: Using CASE Inside HAVING

SELECT department, COUNT(*) AS total_employeesFROM employeesGROUP BY departmentHAVING     CASE         WHEN COUNT(*) > 10 THEN TRUE        ELSE FALSE    END;

🔹 Only shows departments with more than 10 employees.

8. Example: Using CASE with NULL Handling

SELECT name,        CASE            WHEN address IS NULL THEN 'No Address Provided'           ELSE address       END AS address_statusFROM customers;

🔹 Replaces NULL addresses with "No Address Provided".

9. Example: CASE with Multiple Conditions

SELECT name, department, salary,       CASE           WHEN department = 'HR' AND salary > 50000 THEN 'Senior HR'           WHEN department = 'HR' THEN 'HR Staff'           WHEN department = 'IT' THEN 'Tech Team'           ELSE 'Other Department'       END AS job_categoryFROM employees;

🔹 Categorizes employees based on department and salary.

10. Performance Considerations

• CASE executes sequentially, so put the most likely conditions first.
• Use ELSE to handle unexpected cases.
• Combine CASE with indexes for better performance.

Next Steps

✅ Optimize CASE queries
✅ Use CASE in Go Fiber queries
✅ Combine CASE with aggregations, joins, and updates