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/