Prev

PostgreSQL CASE

The CASE expression provides conditional logic in SQL statements, allowing you to dynamically transform values or apply different logic based on conditions. It evaluates each WHEN clause in order and returns the first match.

Key Topics

1. CASE Syntax

A typical CASE statement includes one or more WHEN conditions and an optional ELSE for unmatched cases:

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

2. Example Usage

Here’s how you might classify salaries into categories:

SELECT first_name,
       salary,
       CASE
           WHEN salary < 40000 THEN 'Low'
           WHEN salary BETWEEN 40000 AND 70000 THEN 'Medium'
           ELSE 'High'
       END AS salary_range
FROM employees;

This query labels each employee’s salary based on thresholds. If none of the WHEN conditions match, the result defaults to High in this example.

Best Practices

  • Keep CASE expressions concise and readable; break out complex logic into multiple steps if needed.
  • Use an ELSE clause to handle unexpected cases, ensuring no NULL results unless intended.

Key Takeaways

  • CASE enables in-query conditional statements without resorting to application code.
  • Multiple WHEN clauses provide branching logic; the first matching condition wins.
  • An ELSE clause ensures all possibilities are covered.
Prev