Using Postgres Case in SQL

Introduction

In this article, we will explain the Postgres Case SQL statement, including:

  • What? What does “CASE” do?
  • Why? At what times can we benefit from use of this statement?
  • How and Where? How can we use a “CASE” statement and where/when can we use it in the SQL we write?

Prerequisites

  • Beginner experience writing SQL statements on a PostgreSQL database, using PG Admin or some other database administration tool, or by using code – like Python – in an application that provides a way to connect to your Postgres database with SQL.
  • Beginner level knowledge of how the “SELECT” and “FROM” clauses work in database queries.

What is the “CASE” statement? How does it work? Why use it?

In PostgreSQL, “CASE” evaluates a list of conditions and returns results based on those conditions. Depending on your situation, it can be quite a bit more efficient than using an “IF…ELSE” structure. An important difference is that “IF…ELSE” statements can be used to control the order of execution and “CASE” can not.

Why use “CASE” in our SQL?

Use case: Return honorific based on gender

Let’s begin by looking at a simple use case where we want to query our company’s “Users” table and return an honorific with each user’s record, depending on their gender. In other words, if their gender is “male” then we want to see an honorific of “Mr.” and if their gender is “female” we want “Ms.” to be returned.

Execute the following SQL:

SELECT
t_name_first
, t_name_last
, t_gender
, n_age
FROM Users

You should see the following recordset returned:

t_name_firstt_name_lastt_gendern_age
FranBranfemale35
BobTimsonmale30
GabrielSpankertonmale42
GingerPeppersonunknown29
GandalfGraymale480
BenTuckermale36

Now we’ll use the “CASE” statement to also return a new column called t_honorific. Note, this new column does not exist in our “Users” table; we are using “CASE” to create this new column (field) on the fly. It’s important to note here that here when we say “create,” we merely mean in our returned recordset.

SELECT
t_name_first
, t_name_last
, t_gender
,   CASE
        WHEN t_gender = 'male' THEN 'Mr.'
        WHEN t_gender = 'female' THEN 'Ms.'
    END AS t_honorific
, n_age
FROM Users
t_name_firstt_name_lastt_gendert_honorificn_age
FranBranfemaleMs.35
BobTimsonmaleMr.30
GabrielSpankertonmaleMr.42
GingerPeppersonunknown29
GandalfGraymaleMr.480
BenTuckermaleMr.36

Notice a new column – “t_honorific” – is returned in the database output that is not a column in our “Users” table? Now you probably see how one of our users, Ginger Pepperson, has “unknown” in the “t_gender” column and you may be asking, “What if there are some other genders in the ‘Users’ table other than ‘male’ or ‘female’?” Fortunately, just like the “IF” statement, “CASE” provides us with “ELSE”.

SELECT
t_name_first
, t_name_last
, t_gender
,   CASE
        WHEN t_gender = 'male' THEN 'Mr.'
        WHEN t_gender = 'female' THEN 'Ms.'
        ELSE 'Zee.'
    END AS t_honorific
, n_age
FROM Users
t_name_firstt_name_lastt_gendert_honorificn_age
FranBranfemaleMs.35
BobTimsonmaleMr.30
GabrielSpankertonmaleMr.42
GingerPeppersonZee.29
GandalfGraymaleMr.480
BenTuckermaleMr.36

Notice our change to the query – adding ELSE 'Zee' – returns “Zee.” when their gender is unknown. Note: A bonus is that our “ELSE” also accounts for any NULL values found in the “t_gender” field/column.

Order of execution

An important consideration: The entirety of a “CASE…END” statement is evaluated all at once. Here’s how it works. We’ll take the last query we built above and add one more “WHEN” clause to it:

SELECT
t_name_first
, t_name_last
, t_gender
,   CASE
        WHEN n_age > 200 THEN 'Immortal'
        WHEN t_gender = 'male' THEN 'Mr.'
        WHEN t_gender = 'female' THEN 'Ms.'
        ELSE 'Zee.'
    END AS t_honorific
, n_age
FROM Users

So now if we run this query, we’ll see the following results:

t_name_firstt_name_lastt_gendert_honorificn_age
FranBranfemaleMs.35
BobTimsonmaleMr.30
GabrielSpankertonmaleMr.42
GingerPeppersonZee.29
GandalfGraymaleImmortal480
BenTuckermaleMr.36

What is significant about this? If our “CASE” statement had executed each “WHEN…THEN” clause one at a time, Gandalf would first be evaluated by his age with his honorific being set to “Immortal” and then he would be evaluated based on his gender and the honorific overwritten to be “Mr.”. But that is not what happened. What happened is when one condition was met, i.e., that first one, the value is set, and PostgreSQL moves on.

How and Where?

Here we used “CASE” as part of the “SELECT” clause. “CASE” can also be used with “UPDATE,” “DELETE,” “WHERE,” and “HAVING.” “CASE” statements can also be nested! We’ll save that for a future article.

Conclusion

We learned in this article how to use the “CASE” statement in Postgres SQL queries. We also explored a real world example where one may need to use this statement.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.