PostgreSQL CASE Expression – Adding Conditional Logic to Queries

Welcome to The Coding College, your go-to source for coding and programming tutorials! In this guide, we’ll explore the CASE expression in PostgreSQL, a feature that lets you add conditional logic to your SQL queries.

What is the CASE Expression?

The CASE expression in PostgreSQL is a versatile construct used to perform conditional evaluations. It allows you to return specific values based on logical conditions, making your queries more dynamic and flexible.

Syntax

The CASE expression has two formats:

1. Simple CASE Expression

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

2. Searched CASE Expression

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

Example: Sample Table

Table: students

Example 1: Using Simple CASE

Assign grades based on marks.

SELECT name, marks,  
       CASE  
           WHEN marks >= 85 THEN 'A'  
           WHEN marks >= 70 THEN 'B'  
           WHEN marks >= 50 THEN 'C'  
           ELSE 'F'  
       END AS grade  
FROM students;  

Result:

Example 2: Using CASE with Aggregations

Find the total number of students in each grade category.

SELECT  
    CASE  
        WHEN marks >= 85 THEN 'A'  
        WHEN marks >= 70 THEN 'B'  
        WHEN marks >= 50 THEN 'C'  
        ELSE 'F'  
    END AS grade,  
    COUNT(*) AS total_students  
FROM students  
GROUP BY grade;  

Result:

Example 3: Using CASE in ORDER BY

Sort students by grade category.

SELECT name, marks,  
       CASE  
           WHEN marks >= 85 THEN 'A'  
           WHEN marks >= 70 THEN 'B'  
           WHEN marks >= 50 THEN 'C'  
           ELSE 'F'  
       END AS grade  
FROM students  
ORDER BY grade;  

Result:

Example 4: Combining CASE with Joins

Find the performance level for students based on department categories.

SELECT s.name, d.department_name,  
       CASE  
           WHEN s.marks >= 85 THEN 'Excellent'  
           WHEN s.marks >= 70 THEN 'Good'  
           ELSE 'Average'  
       END AS performance  
FROM students s  
JOIN departments d  
ON s.department_id = d.department_id;  

Use Cases for CASE

1. Data Transformation: Modify or categorize data dynamically in queries.
2. Conditional Aggregations: Apply conditions in GROUP BY or ORDER BY clauses.
3. Simplifying Complex Logic: Replace complex joins or subqueries with a concise expression.

Best Practices

1. Keep Logic Simple: Avoid nesting too many CASE expressions for better readability.
2. Use ELSE Sparingly: Provide a default value with ELSE to handle unexpected cases.
3. Optimize with Indexing: When used in subqueries or joins, ensure indexed columns are part of the logic for improved performance.

Learn More at The Coding College

Dive deeper into PostgreSQL and enhance your SQL skills with our comprehensive guides at The Coding College. We prioritize Google’s E-E-A-T guidelines to deliver expert, authoritative, and user-focused content.

Conclusion

The PostgreSQL CASE expression is a versatile tool for adding conditional logic to SQL queries. By mastering CASE, you can write dynamic queries that cater to complex data requirements.

Stay tuned to The Coding College for more tutorials and tips on PostgreSQL and programming concepts!