PostgreSQL: CASE

The CASE statement is the “if-then-else” of the SQL world. In PostgreSQL, it allows you to add conditional logic directly into your queries without needing external programming languages.

The Two Types of CASE Expressions

PostgreSQL support two formats: General (searched) and Simple.

The General CASE (the “Searched” CASE)

This is the most flexible version. It evaluates a list of boolean conditions and returns the result for the first true condition.

CASE
    WHEN condition_1 THEN result_1
    WHEN condition_2 THEN result_2
    ELSE result_n
END

• How it works: It checks conditions in order. Once a condition is met, it stops and return the value. If nothing is true, it returns the ELSE value (or NULL if no ELSE is provided).

The Simple CASE

This version compares a single expression to a set of values. It’s cleaner but less powerfull bacouse it only checks for equality(=)

CASE expression
    WHEN value_1 THEN result_1
    WHEN value_2 THEN result_2
    ELSE result_n
END

Example

SELECT 
    name, 
    price,
    CASE 
        WHEN price > 100 THEN 'Premium'
        WHEN price BETWEEN 50 AND 100 THEN 'Mid-range'
        ELSE 'Budget'
    END AS price_category
FROM products;

Pro tip: prevent your query from crashing when dividing by zero.

SELECT 
    revenue, 
    units_sold,
    CASE 
        WHEN units_sold = 0 THEN 0
        ELSE revenue / units_sold 
    END AS avg_unit_price
FROM sales;

Pivot Tables (Conditional Aggregation)

You can use CASE inside an aggregate function like SUM() or COUNT() to turn rows into columns.

SELECT 
    department,
    SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) AS completed_tasks,
    SUM(CASE WHEN status = 'Pending' THEN 1 ELSE 0 END) AS pending_tasks
FROM tasks
GROUP BY department;