Code Review Videos > Postgres > How To Use Case In Select Query In Postgres

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:

    when condition1 then result1
    when condition2 then result2
    else default_result
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'
    image: postgres:15.2
      - "5494:5432"
      POSTGRES_DB: app_db
      POSTGRES_USER: app_user
      POSTGRES_PASSWORD: my_top_secret_password
      - "./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:

docker-compose up with our demo postgres database

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.

connect to postgres from jetbrains datagrip

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

select schemas to view in jetbrains datagrip

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

see your docker postgres database in jetbrains data grip

Double click on employees to see the table contents:

employees demo data in datagrip


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.

datagrip select dql icon

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‘:

datagrip 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:

select * from employees result

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:

    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:

select case when outcome postgres

And then we can get more fancy of course:

    when age >= 40 then 'old timer'
    else 'whipper snapper'
  end as ageist_stament,
    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:

more involved select case when outcome

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.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.