The PostgreSQL CASE statement lets you return different values based on conditions in a query. Use it to turn status codes into readable labels, group order amounts into bands, or calculate totals for selected rows.
In a SELECT query, CASE is an expression: it produces a value. It does not run a separate SQL command or change the source data. The examples below use the same small orders table so you can copy the queries and compare your results.
Start with a SELECT CASE example
To classify order amounts, put CASE in the SELECT list and give the result a name with AS.
First, run this setup once in a PostgreSQL session. The temporary table is available only in that session, so keep the same connection open for the remaining examples. If you reconnect, run the setup again.
CREATE TEMP TABLE case_orders (
order_id integer,
amount numeric(10, 2),
status text
);
INSERT INTO case_orders (order_id, amount, status) VALUES
(101, 45.00, 'paid'),
(102, 120.00, 'paid'),
(103, 240.00, 'shipped'),
(104, 80.00, 'pending'),
(105, 300.00, 'cancelled'),
(106, NULL, 'paid');
Now assign an amount band to each order:
SELECT
order_id,
amount,
CASE
WHEN amount IS NULL THEN 'Unknown'
WHEN amount < 50 THEN 'Small'
WHEN amount < 200 THEN 'Medium'
ELSE 'Large'
END AS amount_band
FROM case_orders
ORDER BY order_id;
| order_id | amount | amount_band |
|---|---|---|
| 101 | 45.00 | Small |
| 102 | 120.00 | Medium |
| 103 | 240.00 | Large |
| 104 | 80.00 | Medium |
| 105 | 300.00 | Large |
| 106 | NULL | Unknown |
For each row, PostgreSQL selects the first WHEN condition that is true and returns its THEN value. A false or NULL condition moves the search to the next branch. If none matches, PostgreSQL uses ELSE. If no condition matches and you omit ELSE, the result is NULL.
Order your conditions deliberately. An amount of 45 satisfies both numeric comparisons, but the first match makes it Small. Putting amount < 200 first would incorrectly label it Medium.
These bands assume nonnegative order amounts. Exactly 50 belongs to Medium; exactly 200 belongs to Large. If your data includes refunds as negative amounts, add a separate condition before the amount bands.
The alias amount_band names the calculated output column. It does not add a stored column to case_orders.
Simple CASE vs. searched CASE
Use searched CASE for conditions and simple CASE for matching one expression against specific values. The first example uses searched CASE, also called the general form.
| Form | Shape | Use it for |
|---|---|---|
| Searched CASE | CASE WHEN condition THEN result ... END |
Ranges, NULL checks, multiple conditions |
| Simple CASE | CASE expression WHEN value THEN result ... END |
Mapping codes or categories to labels |
For example, simple CASE can translate the status column:
SELECT
order_id,
CASE status
WHEN 'paid' THEN 'Ready to ship'
WHEN 'shipped' THEN 'Dispatched'
WHEN 'pending' THEN 'Awaiting payment'
WHEN 'cancelled' THEN 'Cancelled'
ELSE 'Unknown status'
END AS status_label
FROM case_orders
ORDER BY order_id;
| order_id | status_label |
|---|---|
| 101 | Ready to ship |
| 102 | Ready to ship |
| 103 | Dispatched |
| 104 | Awaiting payment |
| 105 | Cancelled |
| 106 | Ready to ship |
Here, PostgreSQL compares status with each WHEN value for equality. It returns the corresponding THEN text when it finds a match.
In searched CASE, each condition must be Boolean. The chosen result does not have to be Boolean: these examples return text. Both forms follow the same fallback rule, and their result branches must resolve to a compatible output type. The CASE syntax reference covers both forms.
Combine multiple conditions with AND and OR
You can combine several tests in one WHEN. Suppose an order deserves priority handling only when its status is paid or shipped and its amount is at least 200:
SELECT
order_id,
CASE
WHEN (status = 'paid' OR status = 'shipped')
AND amount >= 200 THEN 'Priority'
ELSE 'Standard'
END AS handling
FROM case_orders
ORDER BY order_id;
Only order 103 receives Priority; the other 5 receive Standard. Order 105 has a large amount, but its cancelled status excludes it.
Keep the parentheses around the OR conditions. Since AND binds more tightly than OR, removing them would let any paid order qualify, regardless of its amount. You could also express the status test as status IN ('paid', 'shipped').
A single searched CASE handles this rule without nesting. If you add more categories, place specific exceptions before broader conditions that could also match them.
Handle NULL values explicitly
Use IS NULL to test for missing data. Neither amount = NULL nor CASE amount WHEN NULL detects a missing amount through ordinary equality.
This query makes the difference visible for order 106:
SELECT
order_id,
amount = NULL AS equality_result,
amount IS NULL AS is_missing,
COALESCE(amount::text, 'Unknown') AS amount_label
FROM case_orders
WHERE order_id = 106;
| order_id | equality_result | is_missing | amount_label |
|---|---|---|---|
| 106 | NULL | true | Unknown |
An ordinary comparison with NULL produces an unknown result, represented by NULL, rather than true. A searched CASE branch is selected only when its condition is true. See the NULL comparison rules for the distinction.
The first example handles missing amounts before testing the bands. Without that branch, the numeric comparisons would not match order 106, and ELSE 'Large' would mislabel it.
For simple null replacement, COALESCE is shorter than CASE. The ::text cast converts the numeric amount to text so it can share an output type with 'Unknown'. Avoid replacing an unknown amount with zero unless that is the meaning your report requires.
Calculate conditional totals with CASE and FILTER
Put CASE inside an aggregate when different rows should contribute different values. This report totals known amounts for paid or shipped orders and counts those orders:
SELECT
SUM(CASE
WHEN status IN ('paid', 'shipped') THEN amount
ELSE 0
END) AS paid_or_shipped_amount,
COUNT(CASE
WHEN status IN ('paid', 'shipped') THEN 1
END) AS paid_or_shipped_orders
FROM case_orders;
| paid_or_shipped_amount | paid_or_shipped_orders |
|---|---|
| 405.00 | 4 |
The total is 45 + 120 + 240. Order 106 is included in the count because its status is paid, but SUM skips its NULL amount. The total covers 3 known amounts; the count covers 4 orders. It does not mean that all 4 orders have a recorded amount.
Do not add ELSE 0 to that COUNT expression. COUNT(expression) counts non-NULL values, including zero, so it would count all 6 orders. Leaving out ELSE makes nonmatching rows return NULL.
PostgreSQL also supports aggregate FILTER syntax:
SELECT
SUM(amount) FILTER (
WHERE status IN ('paid', 'shipped')
) AS paid_or_shipped_amount,
COUNT(*) FILTER (
WHERE status IN ('paid', 'shipped')
) AS paid_or_shipped_orders
FROM case_orders;
This produces the same result for our sample. Prefer FILTER when the job is simply to choose which rows an aggregate receives; use CASE when you also need to transform the values being aggregated.
FILTER applies only to its attached aggregate. A WHERE clause outside the aggregate would remove rows from the input to every aggregate in the SELECT list.
The forms can differ at the edges. If rows exist but none matches, SUM(CASE ... ELSE 0 END) returns zero, while the filtered sum returns NULL. If the entire input is empty, SUM returns NULL in either form. Both COUNT forms return zero when no rows qualify.
COALESCE(SUM(amount) FILTER (WHERE ...), 0) supplies a zero fallback when that is appropriate.
SUM ignores NULL inputs. If every matching amount is NULL, the filtered sum is also NULL; the CASE sum may instead be zero if nonmatching rows supply zeros. See aggregate behavior when defining dashboard totals.
Sort and group your CASE results
Use CASE in ORDER BY when alphabetical order does not match your workflow:
SELECT order_id, status
FROM case_orders
ORDER BY
CASE status
WHEN 'pending' THEN 1
WHEN 'paid' THEN 2
WHEN 'shipped' THEN 3
ELSE 4
END,
order_id;
The resulting order IDs are 104, 101, 102, 106, 103, 105. The second sort key keeps orders with the same status in order-ID order. The numeric priority is used for sorting without appearing in the selected columns.
You can also count orders by a calculated band:
SELECT
CASE
WHEN amount IS NULL THEN 'Unknown'
WHEN amount < 50 THEN 'Small'
WHEN amount < 200 THEN 'Medium'
ELSE 'Large'
END AS amount_band,
COUNT(*) AS orders
FROM case_orders
GROUP BY amount_band
ORDER BY amount_band;
This returns Large: 2, Medium: 2, Small: 1, and Unknown: 1. PostgreSQL lets GROUP BY use this output alias; if an input column has the same name, use a distinct alias to avoid ambiguity.
That convenience does not extend to WHERE or HAVING. To filter on a calculated alias, put the calculation in a subquery or common table expression, then filter in an outer query. If you only need orders worth at least 200, a direct WHERE amount >= 200 is simpler. The SELECT reference explains alias visibility.
Fix common PostgreSQL CASE errors
When a CASE expression fails, check its output types and syntax before adding more branches.
| Problem | What to change |
|---|---|
| Mixing a numeric result with a text label | Convert the number to text for display, or keep all results numeric. |
| Using double quotes around a label | Use single quotes for string values, such as 'Small'. |
Missing END |
Close each CASE expression before its alias. |
| A broad branch catches too many rows | Move narrower conditions above it. |
| Missing values fall into the wrong category | Add an explicit WHEN ... IS NULL branch. |
For a display column that mixes amounts with a label, make the type conversion explicit:
SELECT
order_id,
CASE
WHEN amount IS NULL THEN 'Unknown'
ELSE amount::text
END AS amount_label
FROM case_orders
ORDER BY order_id;
Keep the original numeric column for arithmetic. A text display label is useful to a reader, but it is a poor input for a later SUM. PostgreSQL’s type-resolution rules explain how result branches are reconciled.
Also, do not treat CASE as a universal error guard. A constant expression such as 1 / 0 can fail during query planning even inside an unused branch. Aggregates can be evaluated before an outer CASE chooses its result, too. Protect or exclude invalid inputs at the operation that needs them; the expression-evaluation rules describe these exceptions.
Once the labels and totals match your business rules, you can use the query as the basis for a report. Our guide to analyzing PostgreSQL data in Power BI covers the next connection step.
PostgreSQL CASE FAQ
Does CASE change the data in my table?
CASE in a SELECT changes the query’s output, not the stored rows. You can use a CASE expression inside an UPDATE to choose new stored values, but the UPDATE performs the modification. For the examples in this guide, the calculated labels exist only in the query results.
These SQL expressions end with END. PL/pgSQL also has a separate procedural CASE statement ending with END CASE for choosing which statements to execute.
Can one CASE expression return multiple columns?
A CASE expression returns one value, so use a separate expression for each output column you need. PostgreSQL also supports composite values, but a CASE returning one composite value is not the same as adding several separate columns to your SELECT list. Separate expressions are usually easier to read.
Can I use CASE in a WHERE clause?
Yes, provided the WHERE condition ultimately evaluates to a Boolean. Often, ordinary comparisons joined by AND or OR express the filter more clearly. A SELECT output alias is unavailable in the WHERE clause of the same query; filter that alias in an outer query instead.
Comments 0 Responses