PostgreSQL CASE

PostgreSQL CASE Statement

The CASE statement is SQL's way of handling if/then/else logic. It allows you to go through conditions and return a value when the first condition is met.

It is incredibly useful for creating conditional output in your SELECT statements. You can use it to categorize data or create custom labels.

Basic Syntax

A CASE statement always starts with the CASE keyword. It is followed by one or more WHEN ... THEN ... clauses.

An optional ELSE clause can be used for values that don't meet any condition. The statement must end with the END keyword.

SELECT
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE else_result
  END AS new_column_name
FROM table_name;

A Practical Example

Let's categorize products based on their price. We can create a new column called PriceCategory.

If the price is over 30, we'll label it 'Expensive'. If the price is over 20, we'll label it 'Moderate'. Otherwise, we'll label it 'Cheap'.

SELECT ProductName, Price,
  CASE
    WHEN Price > 30 THEN 'Expensive'
    WHEN Price > 20 THEN 'Moderate'
    ELSE 'Cheap'
  END AS PriceCategory
FROM Products;

The order of the WHEN clauses is very important. The CASE statement stops and returns a result as soon as a condition is met.

Another Example

You can use CASE to check for specific values. Let's add a note next to customers from specific countries.

SELECT CustomerName, Country,
  CASE Country
    WHEN 'USA' THEN 'North America'
    WHEN 'Germany' THEN 'Europe'
    WHEN 'Mexico' THEN 'North America'
    ELSE 'Other'
  END AS Continent
FROM Customers;

This is a slightly different syntax, called a "simple" CASE statement. It compares a column's value against several WHEN values.

Using CASE in ORDER BY

You can also use a CASE statement in an ORDER BY clause. This allows for complex, conditional sorting logic.

For example, you could sort customers from 'USA' first, then all others alphabetically.

SELECT CustomerName, Country
FROM Customers
ORDER BY
  CASE
    WHEN Country = 'USA' THEN 1
    WHEN Country = 'UK' THEN 2
    ELSE 3
  END,
  CustomerName;

Summary

The CASE statement provides powerful conditional logic within your SQL queries. It allows you to create new, dynamic columns based on your data. It can be used in SELECT, WHERE, GROUP BY, and ORDER BY clauses.