Postgres Case

Introduction

This tutorial will explain how the Postgres CASE function allows for creating a block of conditional statements within queries. In its general form, Postgre provides two forms of the CASE expressions, with each condition returning a boolean value of true or false. The Postgres CASE expression will provide results similar to the IF/ELSE expressions, when it is used in other programming languages, and is very useful for sorting and quantifying data stored in a table.

Prerequisite

  • PostgreSQL must be properly installed and configured on the local system.

What is Postgres Case Expression

The Postgres CASE expression will provide similar results to the IF/ELSE expressions when the expression is used in other languages.

The basic form of the CASE expression follows:

1
2
3
4
5
6
CASE
      WHEN <codition>  THEN <result>
      WHEN <another_condition>  THEN <another_result>
      [WHEN ...]
      [ELSE result_n]
END

The above code is the general form of the CASE expression where every condition will return a boolean value of true or false. If the condition equates to ‘true’, the CASE expression will return a result based on the condition. Here the other defined CASE code block, within this expression, will not be processed.

When the conditions equate to ‘false’, the CASE expression returns the results based on the condition defined within the ELSE section. Note that if the ELSE clause is removed, then a null value will be returned.

Postgres CASE Example

With a working understanding of what a Postgres CASE expression is, this section will explain a few basic applications and provide some practical examples. Begin by examining the following sample dataset:

1
2
3
4
5
6
7
8
9
10
 p_id       | p_name | p_lastname | p_gender | age
------------+--------+------------+----------+-----
 12142020   | Maria  | Benneton   |  Female  |  31
 12152020   | Julia  | Robe       |  Female  |  25
 12162020   | Mark   | stefan     |  Male    |  40
 12172020   | damian | kruger     |  male    |  51
 12182020   | jenny  | stockstone |  female  |  41
 12192020   | gina   | stewart    |  female  |  21
 12102020   | denver | strange    |  male    |  35
(7 rows)

Now create a basic CASE expression as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
SELECT
     SUM( CASE
      WHEN age <= 35 THEN
         1
      ELSE
         0
      END
   ) AS "ADULT",
   SUM (
      CASE
      WHEN age > 35 AND age <=50 THEN
         1
      ELSE
         0
      END
   ) AS "MIDDLE AGE",
   SUM (
      CASE
      WHEN AGE > 50 THEN
         1
      ELSE
         0
      END
   ) AS "OLD AGE"
FROM
   tbl_patientinfo;

Here is a breakdown of the above CASE expression and the results it should produce:

  • Gets all of the patients with an age of less than or equal to ’35’ years and groups the patients as ‘ADULT’.
  • Gets all of the patients with an age greater than ’35’ years, but not more than ’50’ years, and groups the patients as ‘MIDDLE AGE’.
  • Gets all of the patients with an age greater than ’50’ years and groups those patients as ‘OLD AGE’.

What the code basically does is sum up all of the ones (1) and then groups them accordingly.

The results should resemble those in the following table:

1
2
3
4
 ADULT | MIDDLE AGE | OLD AGE
-------+------------+---------
     4 |          2 |       1
(1 row)

Now execute the following three select statements to verify the results:

FOR THE ADULT COLUMN

1
2
3
4
5
6
7
8
patienDB=# SELECT * FROM tbl_patientinfo WHERE age <= 35;
 p_id       | p_name | p_lastname | p_gender | age
------------+--------+------------+----------+-----
 12042019   | Maria  |  Bennet    |  Female  |  31
 12052019   | Julia  |  Jennison  |  Female  |  25
 12092019   | gina   |  stewart   |  female  |  21
 12102019   | denver |  strange   |  male    |  35
(4 ROWS)

FOR THE MIDDLE AGE COLUMN

1
2
3
4
5
6
patienDB=# SELECT * FROM tbl_patientinfo WHERE age > 35 AND age <= 50;
 p_id       | p_name | p_lastname | p_gender | age
------------+--------+------------+----------+-----
 12062019   |  Mark  | Davidson   |  Male    |  40
 12082019   |  jenny | stockstone |  female  |  41
(2 ROWS)

FOR THE OLD AGE COLUMN

1
2
3
4
5
patienDB=# SELECT * FROM tbl_patientinfo WHERE age > 50;
 p_id       | p_name | p_lastname | p_gender | age
------------+--------+------------+----------+-----
 12072019   | damian |  kruger    |  male    |  51
(1 ROW)

The above results shows the CASE expression worked properly.

Conclusion

This tutorial explained how the Postgres CASE function allows for the creation of a block of conditional statements within queries. The tutorial specifically covered what a Postgres CASE expression is and provided a Postgres CASE example. The article also provided a breakdown of how the CASE expression works along with an example of the results it should produce. Finally the tutorial explained how to execute three select statements to verify the results of the Postgres CASE expression. Remember that the general form of the CASE expression will return a boolean value of true or false. If the value equates to “true,” the CASE expression will return a result based on the condition. If the conditions equate to “false,” the CASE expression returns the result based on the condition defined within the ELSE section. If the ELSE clause is removed, a null value will be returned.

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.