How to CASE WHEN in PostgreSQL

How do I use CASE WHEN to add conditional logic in PostgreSQL?

CASE WHEN lets you build conditional logic inside SQL statements, returning different values based on each row’s data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does CASE WHEN do in PostgreSQL?

CASE WHEN evaluates each row against one or more conditions and returns the first matching result. If no condition matches, the optional ELSE branch supplies a default.

What is the full syntax of CASE WHEN?

Use the searched form for complex Boolean expressions, or the simple form when comparing one expression to many values. Both end with END and usually need an alias in SELECT.

Searched CASE syntax

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

Simple CASE syntax

CASE expression
WHEN value1 THEN result1
WHEN value2 THEN result2
...
ELSE default_result
END AS alias

How do I categorize order totals with CASE WHEN?

Segment customers by purchase size directly in a SELECT. Assign labels such as "High Value", "Medium Value", or "Low Value" based on total_amount thresholds.

How can I use CASE WHEN in computed columns?

Place CASE WHEN inside SELECT to create virtual columns, inside UPDATE to set values conditionally, or in ORDER BY to sort with custom priority.

How to filter rows with CASE WHEN?

Wrap the CASE WHEN in a subquery or CTE, then use its alias in an outer WHERE clause. PostgreSQL does not allow CASE directly inside WHERE for filtering.

Best practices for CASE WHEN

Keep conditions mutually exclusive to avoid ambiguity. Order WHEN clauses from most-specific to least-specific. Always include an ELSE to handle unexpected data.

Common mistakes and fixes

Forgetting END or an alias

Omit END and PostgreSQL raises “CASE construct not terminated.” Always add END and, in SELECT, follow with AS new_column.

Mixing simple and searched forms

Writing CASE expression WHEN condition>10 THEN ... is invalid. Use searched form: CASE WHEN expression>10 THEN ...

FAQ

Is CASE WHEN the same as IF…ELSE?

Yes in effect, but CASE WHEN is an expression, not a control-flow statement, so it can appear inside SELECT, ORDER BY, GROUP BY, and UPDATE.

Can CASE WHEN use subqueries?

Yes. The THEN clause may return the result of a scalar subquery, such as (SELECT AVG(price) FROM Products).

Does CASE WHEN hurt performance?

Minimal impact. It is evaluated row-by-row during execution. Use indexes on columns referenced in WHEN conditions for best speed.

Why How to CASE WHEN in PostgreSQL is important