How to Use CASE WHEN in PostgreSQL

Galaxy Glossary

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

CASE WHEN adds conditional logic to SQL statements, returning the first matching value or expression, much like an inline IF-ELSE.

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

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

What does CASE WHEN do in PostgreSQL?

CASE WHEN evaluates conditions in the order written and returns the result for the first true condition; if none match, the optional ELSE result is used. This lets you label, transform, or bucket data without altering source tables.

How do I write a simple CASE WHEN expression?

Start with CASE, add one or more WHEN condition THEN result pairs, optionally finish with ELSE default, then close with END.Embed the block inside SELECT, UPDATE, ORDER BY, or WHERE.

Example: Flag high-value orders

SELECT id, CASE WHEN total_amount > 500 THEN 'High' ELSE 'Regular' END AS order_tierFROM Orders;

Where can I place CASE WHEN?

Use it in SELECT to create derived columns, in ORDER BY to sort by custom rules, in UPDATE to change values conditionally, or in WHERE for advanced filtering.

SELECT clause

Label each product’s stock status: SELECT name, CASE WHEN stock = 0 THEN 'Out of Stock' WHEN stock < 10 THEN 'Low' ELSE 'In Stock' END AS statusFROM Products;

ORDER BY clause

Prioritize unfulfilled orders: SELECT * FROM OrdersORDER BY CASE WHEN total_amount IS NULL THEN 1 ELSE 0 END, order_date;

How do I chain multiple conditions?

Order conditions from most to least specific.PostgreSQL checks top to bottom and stops at the first true match, so place narrow filters first to avoid unintended matches.

Best practices for CASE WHEN

Keep blocks readable with line breaks, alias computed columns, always supply ELSE when NULL is unacceptable, and avoid deeply nested CASE statements—consider CTEs for clarity.

.

Why How to Use CASE WHEN in PostgreSQL is important

How to Use CASE WHEN in PostgreSQL Example Usage


-- Send an apology email when an order ships late
SELECT c.email,
       'Apology for order #' || o.id AS subject,
       CASE
           WHEN EXTRACT(DAY FROM NOW() - o.order_date) > 7 THEN 'We are sorry your order is late.'
           ELSE 'Thank you for your purchase!'
       END AS body
FROM Orders o
JOIN Customers c ON c.id = o.customer_id;

How to Use CASE WHEN in PostgreSQL Syntax


CASE
    WHEN <condition1> THEN <result1>
    [WHEN <conditionN> THEN <resultN> ...]
    [ELSE <default_result>]
END

-- Ecommerce examples
-- 1. Categorize orders by spend level
SELECT id,
       CASE
           WHEN total_amount >= 1000 THEN 'Platinum'
           WHEN total_amount >= 500  THEN 'Gold'
           WHEN total_amount >= 200  THEN 'Silver'
           ELSE 'Bronze'
       END AS spend_tier
FROM Orders;

-- 2. Conditional update of product stock
UPDATE Products
SET stock = stock - oi.qty
FROM (
    SELECT product_id AS pid, SUM(quantity) AS qty
    FROM OrderItems
    GROUP BY product_id
) AS oi
WHERE Products.id = oi.pid
  AND CASE WHEN stock - oi.qty < 0 THEN FALSE ELSE TRUE END;

Common Mistakes

Relying on implicit NULL ELSE. Without an explicit ELSE, unmatched rows return NULL, which can break analytics or constraints. Always add ELSE with a clear default.
Placing broad conditions first. PostgreSQL stops at the first true WHEN, so generic checks can capture rows meant for later, specific conditions. Order conditions from specific to generic.

Frequently Asked Questions (FAQs)

Can CASE WHEN be used in a WHERE clause?

Yes. Wrap the CASE WHEN inside the comparison, but ensure it resolves to a boolean. Example: WHERE CASE WHEN total_amount > 100 THEN TRUE ELSE FALSE END.

Is CASE WHEN slower than multiple queries?

Generally no; it is processed within a single scan, often making queries faster than multiple UNIONs. Indexes still apply to referenced columns.

Can I nest CASE WHEN statements?

Yes, PostgreSQL supports nested CASE expressions, but readability suffers. Prefer CTEs or subqueries when logic becomes complex.

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.