CASE expression in PostgreSQL

Introduction

The CASE expression helps you choose different results based on conditions, like making decisions in your data.

You want to show different labels for numbers, like 'Low', 'Medium', or 'High' based on scores.

You need to replace NULL values with a default text in your query results.

You want to group data into categories directly in your query output.

You want to perform simple if-then-else logic inside a SELECT statement.

You want to create custom sorting or filtering based on conditions.

Syntax

PostgreSQL

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE result_default
END

The CASE expression checks each WHEN condition in order and returns the result for the first true condition.

The ELSE part is optional; if no conditions match and ELSE is missing, the result is NULL.

Examples

This example assigns a performance label based on the score value.

PostgreSQL

SELECT
  CASE
    WHEN score >= 90 THEN 'Excellent'
    WHEN score >= 75 THEN 'Good'
    ELSE 'Needs Improvement'
  END AS performance
FROM students;

This example groups people into 'Minor' or 'Adult' based on their age.

PostgreSQL

SELECT
  name,
  CASE
    WHEN age < 18 THEN 'Minor'
    ELSE 'Adult'
  END AS age_group
FROM people;

This example checks if the price is missing and labels it accordingly.

PostgreSQL

SELECT
  product,
  price,
  CASE
    WHEN price IS NULL THEN 'Price not available'
    ELSE 'Price available'
  END AS price_status
FROM products;

Sample Program

This query creates a sales table, inserts some amounts, and uses CASE to categorize each sale by amount.

PostgreSQL

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  amount INTEGER
);

INSERT INTO sales (amount) VALUES (50), (150), (250), (NULL);

SELECT
  id,
  amount,
  CASE
    WHEN amount IS NULL THEN 'No Sale'
    WHEN amount < 100 THEN 'Small Sale'
    WHEN amount BETWEEN 100 AND 200 THEN 'Medium Sale'
    ELSE 'Large Sale'
  END AS sale_category
FROM sales
ORDER BY id;

Important Notes

CASE expressions can be used in SELECT, WHERE, ORDER BY, and other clauses.

You can nest CASE expressions inside each other for more complex logic.

Always test your CASE logic with sample data to ensure it behaves as expected.

Summary

CASE lets you return different results based on conditions in your query.

It works like simple if-then-else logic inside SQL.

Use WHEN for conditions and ELSE for a default result if no conditions match.