PostgreSQL CASE Expression

The CASE expression in PostgreSQL provides conditional logic similar to if-then-else statements in programming languages. It allows you to return different values based on conditions within a SQL query.

Syntax

-- Simple CASE (equality comparison)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE default_result
END

-- Searched CASE (complex conditions)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Official Documentation

Example usage

-- Simple CASE for status translation
SELECT
    order_id,
    CASE status
        WHEN 'P' THEN 'Pending'
        WHEN 'S' THEN 'Shipped'
        WHEN 'D' THEN 'Delivered'
        WHEN 'C' THEN 'Cancelled'
        ELSE 'Unknown'
    END AS status_label
FROM orders;

-- Searched CASE for categorization
SELECT
    product_name,
    price,
    CASE
        WHEN price < 10 THEN 'Budget'
        WHEN price BETWEEN 10 AND 50 THEN 'Mid-range'
        WHEN price > 50 THEN 'Premium'
        ELSE 'Unpriced'
    END AS price_category
FROM products;

-- CASE in aggregations
SELECT
    COUNT(*) AS total_orders,
    COUNT(CASE WHEN status = 'completed' THEN 1 END) AS completed_count,
    COUNT(CASE WHEN status = 'cancelled' THEN 1 END) AS cancelled_count,
    SUM(CASE WHEN status = 'completed' THEN total_amount ELSE 0 END) AS completed_revenue
FROM orders;

-- CASE in ORDER BY
SELECT name, age
FROM users
ORDER BY
    CASE
        WHEN age IS NULL THEN 1
        ELSE 0
    END,
    age;

-- Nested CASE expressions
SELECT
    employee_name,
    department,
    salary,
    CASE
        WHEN department = 'Sales' THEN
            CASE
                WHEN salary > 50000 THEN 'Senior Sales'
                ELSE 'Junior Sales'
            END
        WHEN department = 'Engineering' THEN
            CASE
                WHEN salary > 80000 THEN 'Senior Engineer'
                ELSE 'Junior Engineer'
            END
        ELSE 'Other'
    END AS job_level
FROM employees;

-- CASE for NULL handling
SELECT
    username,
    CASE
        WHEN email IS NOT NULL THEN email
        WHEN phone IS NOT NULL THEN 'Contact via phone'
        ELSE 'No contact info'
    END AS contact_method
FROM users;

-- Conditional updates
UPDATE products
SET discount = CASE
    WHEN category = 'Clearance' THEN 0.50
    WHEN category = 'Sale' THEN 0.25
    WHEN quantity > 100 THEN 0.10
    ELSE 0.00
END;

-- CASE with aggregate conditions
SELECT
    user_id,
    COUNT(*) AS order_count,
    CASE
        WHEN COUNT(*) >= 10 THEN 'VIP'
        WHEN COUNT(*) >= 5 THEN 'Regular'
        ELSE 'New'
    END AS customer_tier
FROM orders
GROUP BY user_id;

Common issues

1. NULL comparisons: CASE column WHEN NULL doesn't work. Use CASE WHEN column IS NULL.
2. Data type consistency: All THEN results must be compatible types or NULL.
3. ELSE clause: If omitted and no conditions match, returns NULL.
4. Short-circuit evaluation: PostgreSQL evaluates conditions in order and stops at first match.

Best practices

1. Always include an ELSE clause to handle unexpected cases explicitly.
2. Use searched CASE for complex conditions, simple CASE for equality checks.
3. For simple NULL checks, consider using COALESCE or NULLIF instead.
4. Order conditions from most specific to most general for better readability.

Frequently Asked Questions

Q: What's the difference between simple CASE and searched CASE?
A: Simple CASE compares one expression against multiple values: CASE status WHEN 'active' THEN.... Searched CASE evaluates independent conditions: CASE WHEN status = 'active' AND age > 18 THEN.... Searched CASE is more flexible.

Q: Can I use CASE in a WHERE clause?
A: Yes, but it's often unnecessary. Instead of WHERE CASE WHEN condition THEN 1 ELSE 0 END = 1, use WHERE condition directly. However, CASE can be useful for complex conditional filtering.

Q: What happens if no WHEN conditions match and there's no ELSE?
A: The CASE expression returns NULL. Always include an ELSE clause for clarity and to avoid unexpected NULLs.

Q: How do I check for NULL in a CASE expression?
A: Use searched CASE with IS NULL: CASE WHEN column IS NULL THEN 'empty' ELSE column END. Simple CASE doesn't work with NULL: CASE column WHEN NULL will never match.

Q: Can CASE expressions be nested?
A: Yes, you can nest CASE expressions, but deep nesting reduces readability. Consider breaking complex logic into multiple columns or using joins/subqueries instead.