Case Statements in PostgreSQL
If you’ve ever had a database table that uses enums (just an integer) or some kind of mysterious code to signify meaning, then you’ve probably reached for a case statement in your SELECT
to output that nonsense into a human-friendly value. If you haven’t, oh boy do I have a treat for you.
For this example, let’s pretend I have a table called home_listings
and that table has several columns – one is a category
field that uses a strange code and the other is a status
field that uses an integer. eww…
nickname | location | category | status |
---|---|---|---|
Bob's seaward adventure | Kitty Hawk, NC | H33Y | 2 |
victorian fixer-upper | Denver, CO | SF78 | 1 |
downtown penthouse | Pittsburgh, PA | SX5F | 3 |
I’d like to output this to something I can understand. All the fun happens inside the SELECT
statement since this is display logic and not querying logic.
SELECT
hl.nickname,
hl.location,
hl.category,
CASE
WHEN hl.category = 'H33Y' THEN 'aquatic dwelling'
WHEN hl.category = 'SF78' THEN 'single family'
WHEN hl.category = 'SX5F' THEN 'apartment complex'
WHEN hl.category = 'MX85' THEN 'mobile home'
END AS category_to_s,
hl.status,
CASE
WHEN hl.status = 0 THEN 'unlisted'
WHEN hl.status = 1 THEN 'for sale'
WHEN hl.status = 2 THEN 'for rent'
WHEN hl.status = 3 THEN 'contract pending'
WHEN hl.status = 4 THEN 'sold'
WHEN hl.status = 5 THEN 'removed'
END AS status_to_s,
FROM home_listings hl
ORDER BY hl.created_at DESC
;
It looks a lot like a case statement in ruby. Easy peasy.
Any now my eyeballs are happier because look at this hot new output:
nickname | location | category | category_to_s | status | status_to_s |
---|---|---|---|---|---|
Bob's seaward adventure | Kitty Hawk, NC | H33Y | aquatic dwelling | 2 | for rent |
victorian fixer-upper | Denver, CO | SF78 | single family | 1 | for sale |
downtown penthouse | Pittsburgh, PA | SX5F | apartment complex | 3 | contract pending |
Here are the docs: https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-case/