How to Use CASE WHEN in PostgreSQL

Galaxy Glossary

How do I use CASE WHEN in PostgreSQL to conditionally return values?

CASE WHEN returns different values based on Boolean conditions evaluated row-by-row.

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

Description

What does CASE WHEN do in PostgreSQL?

CASE WHEN lets you branch logic inside a SELECT, UPDATE, or ORDER BY. It checks conditions top-down and returns the first matching result, giving you IF/ELSE behavior without procedural code.

How is CASE WHEN structured?

Write CASE, then one or more WHEN condition THEN value pairs, optionally an ELSE value, and finish with END.PostgreSQL evaluates each row individually.

Why use CASE WHEN instead of multiple queries?

CASE WHEN keeps transformations in a single SQL statement, reducing round trips and ensuring consistent logic across rows.

Real-world example: categorize order totals

Use CASE WHEN to label Orders as ‘Small’, ‘Medium’, or ‘Large’ based on total_amount. This enables instant dashboard grouping without editing application code.

Can I nest CASE WHEN statements?

Yes. Place another CASE inside the THEN or ELSE clause.Keep nesting minimal for readability.

Does CASE WHEN work in WHERE clauses?

No. WHERE expects a Boolean. Instead, move CASE WHEN to a subquery or use OR/AND directly in WHERE.

Best practice: list most selective conditions first?

Yes. If the first WHEN matches most rows, evaluation stops early and saves CPU.

.

Why How to Use CASE WHEN in PostgreSQL is important

How to Use CASE WHEN in PostgreSQL Example Usage


SELECT
    c.name,
    o.order_date,
    CASE
        WHEN o.total_amount >= 500 THEN 'VIP'
        WHEN o.total_amount >= 200 THEN 'Preferred'
        ELSE 'Standard'
    END AS customer_tier
FROM Customers c
JOIN Orders o ON o.customer_id = c.id
ORDER BY o.order_date DESC;

How to Use CASE WHEN in PostgreSQL Syntax


CASE WHEN syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    [...]
    ELSE default_result
END [AS alias]

Example in ecommerce context:

SELECT
    id,
    total_amount,
    CASE
        WHEN total_amount < 100 THEN 'Small'
        WHEN total_amount BETWEEN 100 AND 499.99 THEN 'Medium'
        WHEN total_amount >= 500 THEN 'Large'
        ELSE 'Unknown'
    END AS order_size
FROM Orders;

Common Mistakes

Forgetting END keyword: Omitting END causes a syntax error. Always close CASE blocks with END before optional aliasing.
Placing CASE WHEN in WHERE: WHERE expects Boolean, so use OR/AND there or wrap CASE in a subquery and filter on its alias.

Frequently Asked Questions (FAQs)

Can CASE WHEN return different data types?

No. All THEN and ELSE branches must be implicitly castable to a single data type. Use explicit CAST() when needed.

Is performance impacted by many WHEN clauses?

Minorly. PostgreSQL evaluates sequentially, so order conditions by likelihood. Indexes are unaffected.

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