Labels with CASE
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 13 of 20.
CASE creates a value by checking conditions in order. WHEN introduces a condition, THEN supplies its value, and ELSE supplies the fallback. END closes the expression.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT id, CASE WHEN price >= 20 THEN 'premium' ELSE 'standard' END AS tier FROM products ORDER BY id;Ids 3 and 5 receive premium; the remaining rows receive standard. CASE changes an output value rather than filtering rows. With several WHEN branches, the first true condition wins.
Use CASE WHEN condition THEN value ELSE fallback END to label each row.
Challenge
EasyReturn id and availability: use sold_out when stock is zero, low when stock is below 4, and ready otherwise. Order by id.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
3Build Useful Results
Calculate and Name ColumnsUnique Results with DISTINCTLabels with CASEFallbacks with COALESCEPacking LabelsPractice on your own: PostgreSQL playground