CASE Expression in PostgreSQL

The CASE expression is SQL’s built-in conditional logic. Because it is an expression rather than a statement, you can use it anywhere a value is expected—inside SELECT, WHERE, GROUP BY, ORDER BY, and aggregate functions like SUM and COUNT. PostgreSQL evaluates each WHEN condition in order and returns the result of the first one that is true.

Syntax

General form — evaluates arbitrary Boolean conditions:

CASE
  WHEN condition_1 THEN result_1
  WHEN condition_2 THEN result_2
  ...
  ELSE else_result
END

Simple form — compares one expression against a list of values (equality only):

CASE expression
  WHEN value_1 THEN result_1
  WHEN value_2 THEN result_2
  ...
  ELSE else_result
END

Practical Example

Create a products table and classify items by price tier using the general form:

CREATE TABLE products (
  id         SERIAL PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  unit_price NUMERIC(10, 2) NOT NULL,
  category   VARCHAR(50)
);

INSERT INTO products (name, unit_price, category) VALUES
  ('Economy Widget',    4.99,  'widgets'),
  ('Standard Widget',  24.99,  'widgets'),
  ('Premium Widget',   89.99,  'widgets'),
  ('Enterprise Widget', 249.99, 'widgets');

SELECT
  name,
  unit_price,
  CASE
    WHEN unit_price <  10.00 THEN 'Budget'
    WHEN unit_price <  50.00 THEN 'Mid-range'
    WHEN unit_price < 100.00 THEN 'Premium'
    ELSE 'Enterprise'
  END AS price_tier
FROM products
ORDER BY unit_price;

Expected output:

        name          | unit_price | price_tier
----------------------+------------+------------
 Economy Widget       |       4.99 | Budget
 Standard Widget      |      24.99 | Mid-range
 Premium Widget       |      89.99 | Premium
 Enterprise Widget    |     249.99 | Enterprise

Use the simple form to expand a short status code into a label:

SELECT
  id,
  name,
  CASE category
    WHEN 'widgets'     THEN 'Widget Products'
    WHEN 'accessories' THEN 'Accessories'
    WHEN 'services'    THEN 'Service Items'
    ELSE 'Uncategorized'
  END AS category_label
FROM products;

Conditional Aggregation

A powerful pattern is combining CASE with aggregate functions to produce a pivot-style breakdown in a single query pass:

SELECT
  SUM(CASE WHEN unit_price <  10.00               THEN 1 ELSE 0 END) AS budget_count,
  SUM(CASE WHEN unit_price >= 10.00 AND unit_price < 50.00  THEN 1 ELSE 0 END) AS midrange_count,
  SUM(CASE WHEN unit_price >= 50.00 AND unit_price < 100.00 THEN 1 ELSE 0 END) AS premium_count,
  SUM(CASE WHEN unit_price >= 100.00               THEN 1 ELSE 0 END) AS enterprise_count
FROM products;

Use CASE in ORDER BY to apply a custom sort order without altering stored data:

SELECT name, unit_price
FROM products
ORDER BY
  CASE
    WHEN unit_price <  10.00 THEN 1
    WHEN unit_price <  50.00 THEN 2
    WHEN unit_price < 100.00 THEN 3
    ELSE 4
  END;

Inspect how PostgreSQL plans a conditional aggregation to make sure it is not doing unnecessary work:

EXPLAIN (ANALYZE, BUFFERS)
SELECT
  SUM(CASE WHEN unit_price < 10.00 THEN 1 ELSE 0 END) AS budget_count
FROM products;

Testing with Vela

Before shipping a CASE-heavy reporting query to production, clone the production database to a Vela branch and run the query there. You can iterate on the CASE conditions, verify the output against real data distributions, and confirm the execution plan with EXPLAIN ANALYZE—all without risking production query performance or locking any live tables.

Production Tips

• Always include an ELSE clause. Without it, rows that match no WHEN return NULL, which can silently corrupt aggregations.
• Place the most common or most selective WHEN conditions first—PostgreSQL evaluates them in order and short-circuits on the first match.
• Avoid CASE in WHERE clauses when performance matters; index access is typically lost. Rewrite as OR conditions or add a computed column with an expression index.
• Conditional aggregation with SUM(CASE WHEN ... THEN 1 ELSE 0 END) is the standard SQL way to pivot row counts into columns without a dedicated PIVOT syntax.
• In ORDER BY, use CASE to implement arbitrary sort priority rather than storing a sort-order column in the table.
• For complex branching logic shared across many queries, consider extracting the CASE into a stored function or a generated column for maintainability.