How to CASE WHEN in PostgreSQL

Galaxy Glossary

How do I use CASE WHEN in PostgreSQL for conditional logic?

CASE WHEN returns a calculated value based on the first true condition, enabling inline IF-ELSE logic in any SQL clause.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

What is the purpose of CASE WHEN in PostgreSQL?

CASE WHEN lets you embed IF-ELSE logic directly in SQL. It evaluates conditions in order and returns the first matching result, or the ELSE result when none match.

How do I write a basic CASE WHEN?

Use the searched form: CASE WHEN condition THEN result [ELSE default] END. Alias the expression for readability.

Where can I place CASE WHEN?

You can use it in SELECT, ORDER BY, GROUP BY, HAVING, UPDATE, and even inside window functions or subqueries.

Can I evaluate multiple conditions?

Yes. Add successive WHEN clauses. The first true condition stops evaluation, so order them from most specific to most general.

Example: classifying order sizes

CASE WHEN total_amount > 1000 THEN 'Large' WHEN total_amount > 500 THEN 'Medium' ELSE 'Small' END AS order_size

How do I handle NULL values?

Explicitly test for IS NULL or co-alesce columns. Otherwise, NULL makes comparisons return UNKNOWN and skip true branches.

Does CASE WHEN impact performance?

Minimal. PostgreSQL short-circuits evaluation. Heavy expressions inside branches may slow execution; move them to CTEs when needed.

Best practice: keep expressions readable

Indent each WHEN, alias the CASE, and comment complex logic. Future maintainers will thank you.

Best practice: provide an ELSE

An explicit ELSE prevents unexpected NULLs and clarifies intent.

Why How to CASE WHEN in PostgreSQL is important

How to CASE WHEN in PostgreSQL Example Usage


-- Flag high-value customers and count their products
SELECT
    c.name,
    c.email,
    CASE
        WHEN SUM(o.total_amount) > 5000 THEN 'Gold'
        WHEN SUM(o.total_amount) BETWEEN 2000 AND 5000 THEN 'Silver'
        ELSE 'Bronze'
    END AS tier,
    COUNT(oi.id) AS items_bought
FROM Customers c
JOIN Orders o       ON o.customer_id = c.id
JOIN OrderItems oi  ON oi.order_id   = o.id
GROUP BY c.id, c.name, c.email;

How to CASE WHEN in PostgreSQL Syntax


-- Searched CASE (most common)
CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    ELSE default_result
END [AS alias]

-- Simple CASE (tests a single expression)
CASE expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    [...]
    ELSE default_result
END [AS alias]

-- Ecommerce example inside SELECT
SELECT
    id,
    name,
    CASE
        WHEN total_amount > 1000 THEN 'VIP'
        WHEN total_amount BETWEEN 500 AND 1000 THEN 'Preferred'
        ELSE 'Standard'
    END AS customer_tier
FROM Orders;

Common Mistakes

Using overlapping conditions makes only the first branch execute, silently hiding later logic. Fix by ordering conditions from strictest to broadest or by rewriting logic.
Omitting ELSE causes NULL returns that break downstream aggregations. Always include an ELSE or wrap the CASE in COALESCE to supply a fallback.

Frequently Asked Questions (FAQs)

Can CASE WHEN appear in a WHERE clause?

No. Use it in SELECT, ORDER BY, GROUP BY, HAVING, or as an update value. For conditional filtering, combine boolean logic directly in WHERE.

Is CASE WHEN the same as IF?

PostgreSQL has no standalone IF in standard SQL. CASE WHEN is the ANSI-compliant replacement and works in any SQL context.

How many WHEN branches can I add?

There is no explicit limit. Practical readability usually caps the list; refactor when you exceed a dozen conditions.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie