How To Use Case In Select Query In Postgres

I’ve come across the select case syntax in Postgres before, but never had to figure out what it does. However on my current project I was faced with a query where I had to understand it, so I went about setting up a little local demo file to figure out how to use case in a select query in Postgres. And I figured I would document my findings for my future reference.

For obvious reasons, I cannot share the true query. However, it doesn’t matter as I have created a repeatable example that demonstrates select case ... in its most basic form. This should be enough to understand what it does, and to give you a place to dive in further if needed.

The select case when ... statement allows you to create conditional expressions within a select statement.

Here’s some basic syntax that broadly explains the concept:

select
  case
    when condition1 then result1
    when condition2 then result2
    ...
    else default_result
  end
from table_name;Code language: SQL (Structured Query Language) (sql)

This is a bit like a switch statement in JavaScript, allowing you to specify multiple conditions and the result you want for each.

The final else clause is the default value if none of the other conditions are hit.

That may be enough for you. It wasn’t for me. I needed an example and to have a play about.

Setting Up Our Demo Environment

You can find this code over on GitHub.

I’m going to use Docker to quickly spin up a throwaway Postgres DB. For this I will use docker-compose:

version: '3.9'
services:
  db:
    image: postgres:15.2
    ports:
      - "5494:5432"
    environment:
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
    volumes:
      - "./init.sql:/docker-entrypoint-initdb.d/init.sql"Code language: JavaScript (javascript)

These contents go into a docker-compose.yaml file.

This docker compose file defines a single service named “db”. The service runs a Postgres database version 15.2 using the official Postgres Docker image.

The service exposes port 5494 on the host machine (your local machine), which is mapped to the default Postgres port 5432 in the container. This allows you to access the database from your PC.

The environment variables should hopefully be self explanatory, but they are defined by the official postgres docker image, and I am using the explicit config above to override them to my own values.

The volumes section maps the local file “./init.sql” to the container directory “/docker-entrypoint-initdb.d/init.sql“. This allows you to run any SQL scripts in that file when the container is started up, which is very useful for initialising the database schema and loading our demo data.

Here’s the corresponding init.sql file:

create table employees (
  id serial primary key,
  name varchar(50) not null,
  age integer not null,
  salary integer not null
);

insert into employees (name, age, salary) values
('john smith', 32, 45000),
('jane doe', 27, 55000),
('bob johnson', 45, 65000),
('mary brown', 38, 75000),
('david lee', 29, 35000);Code language: JavaScript (javascript)

You can then spin this up with docker-compose up:

That’s our database up and running, and it will sit there until we hit ctrl + c in the terminal to kill the session.

I use JetBrains DataGrip for connecting to the database.

Be sure to switch to the Schemas tab also, and tick the box for app_db > All schemas:

With that done, you should now see your DB in the Database Explorer pane:

Double click on employees to see the table contents:

Sweet.

Running Queries Against Postgres With JetBrains DataGrip

Let’s quickly cover how to actually run SQL queries from inside JetBrains DataGrip.

Depending on your GUI, how you do this will differ. In JetBrains DataGrip I find this process quite unintuitive.

What you need to do is ensure you have selected the database you are wanting to interact with, and then click the little terminal icon – I think it says dql but it’s so tiny I can’t be absolutely sure.

It’s the icon between the two blue back / forwards arrows, and the funnel icon. Or, I guess, hit the shortcut keys.

From there I select ‘Open Default Console‘:

OK, you should now have your query window open.

A good test is to run a basic query:

select * from employees;Code language: SQL (Structured Query Language) (sql)

Which should yield all the results in the table:

Assuming that works, we are good to go.

select case when by Example

We now want to run our select case when ... query.

Our query should look at the salary column, then give a text outcome based on:

• 70,000+ = “yacht club”
• 50,000-69,999 = “golf club”
• 40,000-49,999 = “better than average”
• < 40,000 = “below average”

OK, a bit silly, but that’s what we are going with.

Here’s the query then:

select 
  name,
  case
    when salary >= 70000 then 'yacht club'
    when salary >= 50000 and salary < 70000 then 'golf club'
    when salary >= 40000 and salary < 50000 then 'better than average'
    else 'below average'
  end as our_text_based_outcome
from employees;Code language: SQL (Structured Query Language) (sql)

And the outcome is as expected:

And then we can get more fancy of course:

select
  name,
  age,
  case
    when age >= 40 then 'old timer'
    else 'whipper snapper'
  end as ageist_stament,
  salary,
  case
    when salary >= 70000 then 'yacht club'
    when salary >= 50000 and salary < 70000 then 'golf club'
    when salary >= 40000 and salary < 50000 then 'better than average'
    else 'below average'
  end as our_text_based_outcome
from employees;Code language: SQL (Structured Query Language) (sql)

Which gives:

So really, not that complicated at all.

It’s a way to provide a further value based on the initial value, with an optional fall back value.

You can muck around with the values, change the case statements etc, just to get your hands dirty. But I was pleasantly surprised with how easy this one turned out to be.

Docker Tear Down

As above, hit ctrl + c in the terminal window to exit the running container.

However, this container will remain in an exited state.

To wipe this out, run the following command:

docker rm $(docker ps -a -f status=exited -q)Code language: Shell Session (shell)

This will return a string like: “d28cd150987a” which was the container ID of the exited container.

At that point you can run docker ps -a to ensure no exited containers remain. All done.