Using Postgres Case When

Introduction

This tutorial will help you to learn how to use Postgres Case When branching for program control, as well as explore some similar and alternative branching and comparison approaches, including:

Prerequisites

  • Some experience sending SQL statements to 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 WHEN statement?

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

Case When example

Let’s begin by looking at a simple use case where we want to query a table called “tbl_users” and return a pronoun, depending on whether “t_gender” is set to male or female. In other words, if t_gender is “male” then we want Postgres to return “he” and if t_gender is “female” we want “she” to be returned.

Execute the following SQL:

SELECT
t_name_first
, t_name_last
, t_gender
FROM tbl_users

You should see the following recordset returned:

t_name_firstt_name_lastt_gender
FlanBlandfemale
JonMimsonmale
MabrielPankersonmale
MingerFeppersonunknown
DandalfGlaymale
FenSnuckermale

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 'he'
        WHEN t_gender = 'female' THEN 'she'
    END AS t_pronoun
tbl_users;
t_name_firstt_name_lastt_gendert_pronoun
FlanBlandfemaleshe
JonMimsonmalehe
MabrielPankersonmalehe
MingerFeppersonunknown
DandalfGlaymalehe
FenSnuckermalehe

Notice a new column – “t_pronoun” – is returned in Postgres’ output that is not a column in our “tbl_users” table? Now you probably see how one of our users, Minger Fepperson, 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 'he'
        WHEN t_gender = 'female' THEN 'she'
        ELSE 'zee'
    END AS t_pronoun
FROM tbl_users;
t_name_firstt_name_lastt_gendert_pronoun
FlanBlandfemaleshe
JonMimsonmalehe
MabrielPankersonmalehe
MingerFeppersonunknownzee
DandalfGlaymalehe
FenSnuckermalehe

Notice our change to the Postgres SQL above where we added ELSE ‘Zee’ – returns “Zee” when their gender is stored in the database as “unknown”. A bonus here with “ELSE” is that it also accounts for any NULL values found in the “t_gender” column.

Case order of execution

All of a “CASE…END” statement is evaluated all at once. Here’s how it works. Looking at the SQL we wrote above when we add one more WHEN clause to it:

SELECT
t_name_first
, t_name_last
, t_gender
,   CASE
        WHEN t_name_first = 'Flan' THEN 'goddess'
        WHEN t_gender = 'male' THEN 'he'
        WHEN t_gender = 'female' THEN 'she'
        ELSE 'zee'
    END AS t_pronoun
FROM tbl_users;

So now when you run this query, you get the following results:

t_name_firstt_name_lastt_gendert_pronoun
FlanBlandfemalegoddess
JonMimsonmalehe
MabrielPankersonmalehe
MingerFeppersonunknownzee
DandalfGlaymalehe
FenSnuckermalehe

What is significant here? If the “CASE” statement had executed each WHEN … THEN statement as you might suspect, Flan would first be evaluated by her first name with her pronoun being set to “goddess” and then she would be evaluated based on her gender and the pronoun overwritten to become “she”. But that is not what happens. What happens is when one condition was met – the first one in this case – the value to return is set, and Postgres moves on to the line past “END AS”.

Postgres Coalesce

In PostgreSQL, the Coalesce statement has much in common with the CASE WHEN we studied above. We’ll focus here on the syntax of Postgres’ Coalesce, and give an example of using it. Coalesce in Postgres is used to return the first non-Null value in a list of values.

Coalesce example

We’ll begin with the recordset below. Please take note of the row that has a NULL value in the t_gender column:

t_name_firstt_name_lastt_gender
MingerFepperson
FlanBlandfemale
JonMimsonmale
MabrielPankersonmale
DandalfGlaymale
FenSnuckermale
SELECT
t_name_first
, t_name_last
, COALESCE(t_gender, 'unknown') AS t_gender
FROM tbl_users;

Returns

t_name_firstt_name_lastt_gender
MingerFeppersonunknown
FlanBlandfemale
JonMimsonmale
MabrielPankersonmale
DandalfGlaymale
FenSnuckermale

Analysis

PostgreSQL’s Coalesce function returned t_gender’s original value – that is, the value stored in the table – every time EXCEPT for when that value was NULL. When it was NULL, Coalesce returned ‘unknown’. In other words, Coalesce returns the first non-NULL value in a list of values provided to it as parameters.

Postgres IF statement

Perhaps the most simple of control structures comparable to – or which can sometimes be used instead of – using Postgres Case When, is the ubiquitous IF conditional.

Postgres IF syntax

IF ([TRUE statement]) THEN
    statements TO EXECUTE
END IF;

Postgres IF example

IF (b_verified IS NOT NULL) THEN
    UPDATE tbl_users SET b_active=TRUE;
END IF;

Postgres IF ELSE statement

In case you need to specify an alternative when IF evaluates to false, we use PostgreSQL’s ELSE statement.

Postgres IF ELSE syntax

IF ([TRUE statement]) THEN
    statements TO EXECUTE
ELSE
    other statements TO EXECUTE
END IF;

Postgres IF ELSE example

IF (b_verified IS NOT NULL) THEN
    UPDATE tbl_users SET b_active=TRUE;
ELSE
    UPDATE tbl_users SET b_active=FALSE;
END IF;

What if we have more than one possible value to deal with and for whatever reason, using Postgres’ Case When does not serve our needs?

Postgres ELSIF

Postgres ELSIF syntax

IF ([TRUE statement]) THEN
    statements TO EXECUTE
ELSEIF ([TRUE statement]) THEN
    other statements TO EXECUTE
ELSEIF ([TRUE statement]) THEN
    other statements TO EXECUTE
ELSE
    other statements TO EXECUTE
END IF;

Postgres ELSIF example

IF (i_level = 4) THEN
    UPDATE tbl_users SET b_owner = TRUE;
ELSIF (i_level = 3)
    UPDATE tbl_users SET b_admin = TRUE;
ELSE
    UPDATE tbl_users SET b_normie = TRUE;
END IF;

Conclusion

We learned in this article how to use the Postgres CASE WHEN statement in SQL. We looked at syntax and examples. We also explored some related branching methods provided by PostgreSQL, including the Coalesce function and various ways to use IF, ELSIF, and ELSE. Code samples provided for each example.

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.