PostgreSQL CASE WHEN: Complete Guide with Examples

PostgreSQL CASE WHEN: Conditional Logic in Queries<

The CASE WHEN expression in PostgreSQL provides conditional logic within SQL queries. This guide covers syntax, usage examples, and practical applications.

What is CASE WHEN in PostgreSQL?

The CASE WHEN expression is used to implement conditional logic in SQL queries. It evaluates conditions and returns specific results based on whether the condition is true or false. It's commonly used in SELECT statements, but can also be used in WHERE, ORDER BY, and other SQL clauses.

Syntax:

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

Explanation:

• WHEN: Specifies a condition to evaluate.
• THEN: Defines the result if the condition is true.
• ELSE: (Optional) Specifies a default result if no conditions are met.
• END: Marks the end of the CASE expression.

Example 1: Basic Usage in a SELECT Statement

Code:

-- Select employees and categorize their salaries
SELECT 
    employee_name,
    salary,
    CASE
        WHEN salary > 70000 THEN 'High'
        WHEN salary BETWEEN 50000 AND 70000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category
FROM employees;

Explanation:

• Evaluates each employee's salary and categorizes it as 'High', 'Medium', or 'Low'.
• The salary_category column stores the result.

Example 2: Using CASE WHEN in a WHERE Clause

Code:

-- Filter employees based on conditional logic
SELECT 
    employee_name, 
    department 
FROM employees
WHERE 
    CASE 
        WHEN department = 'Sales' THEN 1
        ELSE 0
    END = 1;

Explanation:

• Only rows where the department is 'Sales' are included in the result.

Example 3: Using CASE WHEN in an UPDATE Statement

Code:

-- Update employee bonuses based on their performance
UPDATE employees
SET bonus = 
    CASE
        WHEN performance_rating = 'Excellent' THEN 1000
        WHEN performance_rating = 'Good' THEN 500
        ELSE 100
    END;

Explanation:

• Updates the bonus field based on employees’ performance ratings.

Example 4: Using CASE WHEN in ORDER BY

Code:

-- Order employees by salary categories
SELECT 
    employee_name, 
    salary 
FROM employees
ORDER BY 
    CASE
        WHEN salary > 70000 THEN 1
        WHEN salary BETWEEN 50000 AND 70000 THEN 2
        ELSE 3
    END;

Explanation:

• Orders employees by salary category, with 'High' salaries appearing first.

Best Practices

• Use Default Cases: Always include an ELSE clause to handle unexpected inputs.
• Optimize Complex Logic: Combine CASE WHEN with other SQL functions like COALESCE for cleaner queries.
• Avoid Overuse: For extensive conditions, consider refactoring with stored procedures or application-side logic.

Common Errors

• Missing END Keyword: Always conclude the CASE WHEN expression with END.
• Data Type Mismatch: Ensure all THEN and ELSE results have the same data type.

All PostgreSQL Questions, Answers, and Code Snippets Collection.