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.
select case when ... statement allows you to create conditional expressions within a
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
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
I’m going to use Docker to quickly spin up a throwaway Postgres DB. For this I will use
These contents go into a
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.
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
You can then spin this up with
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:
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)
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.