Comprehensive Guide to PostgreSQL CASE Statement

Using the CASE Statement in PostgreSQL

The CASE statement in PostgreSQL allows for conditional logic within SQL queries. It operates similarly to IF-THEN-ELSE statements in programming languages, enabling dynamic decision-making in queries. This guide covers the syntax, examples, and practical use cases for the CASE statement.

Syntax of CASE Statement

The CASE statement can be used in two forms:

1. Simple CASE: Compares an expression to a set of values.

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

2. Searched CASE: Evaluates boolean expressions.

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

Example 1: Simple CASE in a SELECT Query

Scenario: Assign labels to employee salary ranges.

Code:

-- Select employee names and assign salary ranges
SELECT 
    name,
    salary,
    CASE
        WHEN salary < 3000 THEN 'Low'
        WHEN salary BETWEEN 3000 AND 7000 THEN 'Medium'
        ELSE 'High'
    END AS salary_range
FROM employees;

Explanation:

• The CASE evaluates the salary column for each employee.
• Outputs 'Low', 'Medium', or 'High' based on salary conditions.

Example 2: Searched CASE in a WHERE Clause

Scenario: Filter employees based on dynamic criteria.

Code:

-- Filter employees based on a dynamic salary range
SELECT *
FROM employees
WHERE 
    CASE
        WHEN department = 'HR' THEN salary > 4000
        ELSE salary > 3000
    END;

Explanation:

• For employees in the HR department, filter those earning above 4000.
• For others, include those earning above 3000.

Example 3: Using CASE in UPDATE

Scenario: Update employee bonuses based on performance ratings.

Code:

-- Update bonus amounts based on ratings
UPDATE employees
SET bonus = 
    CASE
        WHEN performance_rating = 'Excellent' THEN 1000
        WHEN performance_rating = 'Good' THEN 500
        ELSE 100
    END;

Explanation:

• Modifies the bonus column based on performance_rating.

Advanced Example: Nested CASE Statements

Scenario: Determine employee rank based on multiple criteria.

Code:

-- Assign ranks based on salary and experience
SELECT 
    name,
    salary,
    experience,
    CASE
        WHEN salary > 7000 THEN 
            CASE
                WHEN experience > 5 THEN 'Senior'
                ELSE 'Mid-Level'
            END
        ELSE 'Junior'
    END AS rank
FROM employees;

Explanation:

• The outer CASE checks salary first.
• The nested CASE further evaluates experience for employees with high salaries.

Practical Applications of CASE

1. Dynamic Reporting: Generate reports with categorized data.

2. Conditional Aggregates: Apply conditions in SUM, COUNT, etc.

3. Data Transformation: Modify data on-the-fly during queries.

4. Flexible Filters: Add conditional logic to filter criteria.

Best Practices

1. Use Default Cases: Always include an ELSE clause to handle unexpected values.

2. Optimize Conditions: Arrange conditions for efficiency, with the most likely cases first.

3. Avoid Redundancy: Combine CASE with other SQL constructs to streamline queries.

All PostgreSQL Questions, Answers, and Code Snippets Collection.