Postgres CASE WHEN

Say Goodbye to Nested SELECTs (and Hello to Sanity!)

Consider this scenario. We have a table named orders with columns order_id and order_status.

All we want to do is retrieve a list of orders, but display different messages for different order statuses.

At high level, our select should look something like this:

select order_id, order_status, <dynamic_order_message>
from orders

Let’s discuss the options we have for computing order_message.

The first one is based on helper table and nested select query. Our general SQL should look like this:

select order_id, order_status,
(select status_message from order_statuses where orders.order_status = order_statuses.status_code) AS status_message
from orders;

The second option is the case-when expression. Look at how convenient it could be:

select order_id, customer_id, order_date,
case order_status
when 'pending' then 'order processed!'
when 'delivered' then 'order delivered!'
else 'Unknown status'
end AS status_message
from orders;

Notice that you don’t have to create a separate table.