PostgreSQL CASE, COALESCE and NULLIF

CASE is SQL’s if/else. The searched form tests conditions in order and returns the first match; the simple form compares one value against several. Without ELSE, a non-matching row gets NULL. CASE works anywhere an expression is allowed: in SELECT, WHERE, ORDER BY, even inside aggregates.

COALESCE(a, b, c) returns the first non-NULL argument, the standard way to show a default instead of NULL. NULLIF(a, b) returns NULL when a = b, which is mostly used to avoid division by zero. GREATEST and LEAST pick the largest or smallest of their arguments.

Syntax

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

CASE value WHEN 'a' THEN ... WHEN 'b' THEN ... END

Examples

Edit the code and run it. Output is printed the way psql prints it.

10.1Price bands

SELECT name, price,
       CASE
         WHEN price >= 200 THEN 'premium'
         WHEN price >= 50  THEN 'mid-range'
         ELSE 'budget'
       END AS band
FROM products
ORDER BY price DESC;

Conditions are checked top to bottom. A 249.00 monitor matches the first one and stops there, even though it also satisfies price >= 50.

10.2Simple CASE and custom sort order

SELECT id, status
FROM orders
ORDER BY CASE status
           WHEN 'pending'   THEN 1
           WHEN 'shipped'   THEN 2
           WHEN 'delivered' THEN 3
           ELSE 4
         END, id;

Text would sort alphabetically. CASE maps each status to a number so they sort in business order.

10.3Pivot with CASE inside aggregates

SELECT c.country,
       count(*) FILTER (WHERE o.status = 'delivered') AS delivered,
       sum(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders o
JOIN customers c ON c.id = o.customer_id
GROUP BY c.country
ORDER BY c.country;

Turning row values into columns is called pivoting. FILTER and sum(CASE ...) are two ways of doing it; the CASE version works in every SQL database.

10.4COALESCE, NULLIF, GREATEST

SELECT name,
       COALESCE(city, 'Unknown city') AS city
FROM customers;

SELECT name, stock,
       round(price / NULLIF(stock, 0), 2) AS price_per_unit_in_stock,
       GREATEST(stock, 50) AS at_least_50
FROM products
ORDER BY stock;

Mia’s NULL city becomes “Unknown city”. For products with zero stock, NULLIF(stock, 0) becomes NULL, and dividing by NULL gives NULL instead of a “division by zero” error.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 10.1

Show each product name, stock and a column availability: “Out of stock” when stock is 0, “Low” when below 50, otherwise “In stock”.

Hint

Searched CASE with three branches.

Solution

SELECT name, stock, CASE WHEN stock = 0 THEN 'Out of stock' WHEN stock < 50 THEN 'Low' ELSE 'In stock' END AS availability FROM products;

Exercise 10.2

For every employee show first_name and dept: the department id as text, or none if NULL.

Hint

COALESCE(dept_id::text, ...)

Solution

SELECT first_name, COALESCE(dept_id::text, 'none') AS dept FROM employees;