Postgres Case Examples

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’ve ever written any code, you’re familiar with conditional expressions such as IF / ELSE and SWITCH cases. These expressions aren’t just used in programming– we can also use the IF / ELSE statement in PostgreSQL. The PostgreSQL CASE is another type of conditional expression used in PostgreSQL. This expression uses a WHEN - THEN structure which is similar to a classic IF / ELSE statement. Each condition in the expression returns a Boolean value which is either TRUE or FALSE. Like an IF statement, the CASE statement can also have an ELSE clause to handle situations where the Boolean value is FALSE. In this article, we’ll take a closer look at the Postgres CASE expression– the WHEN-THEN statement.

Prerequisites

In order to follow along with the examples discussed in this article, you’ll need to have PostgreSQL installed on your computer. You’ll also need some working knowledge of PostgreSQL in order to understand the instructions and examples.

PostgreSQL ‘WHEN’ case

Below is the simple example of CASE syntax

1
2
3
CASE WHEN condition THEN result_1
     ELSE result_2
END

Shown below is a simple example of the CASE syntax used within a SELECT statement:

1
2
3
4
5
SELECT
CASE WHEN condition THEN result_1
     ELSE result_2
END
FROM TABLE_NAME;

PostgreSQL ‘CASE’ example

Our next example will be slightly more complex. We’ll be using the Student table shown below, which contains the id, name and grade of the students:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 id  |       name       | grade
-----+------------------+-------
  32 | Allen James      |    89
 371 | Jerry Lawrel     |    92
 536 | Tommy Jones      |    85
 312 | Michelle Rogers  |    85
 162 | Kelly Ripa       |    87
  51 | Fred Smith       |    89
 322 | Alfred Roosevelt |    89
 481 | Wayne Rosing     |    91
 261 | Samuel Davis     |    92
 621 | Philip Wilson    |    95
 782 | Jay Leno         |    93
 361 | MAX Heindel      |    97
 891 | Gerry Starr      |    94

PostgreSQL ‘INSERT INTO’ example

If you’d like to create this sample table to use as you follow along with our examples, you’ll first need to have a PostgreSQL database available. Once you create a database using the CREATE DATABASE SQL keywords, you can then connect to it with the \c command. After that, you can use the following CREATE TABLE statement to create a table for the data:

1
2
CREATE TABLE student
(id INT PRIMARY KEY, name VARCHAR(50), grade INT);

It should respond with CREATE TABLE if the command was successful. You can then use the following SQL statement to insert some test data to use in a CASE WHEN query:

1
2
3
4
5
6
7
8
INSERT INTO student (id, name, grade)
VALUES (32, 'Allen James', 89), (371, 'Jerry Lawrel', 92),
(536, 'Tommy Jones', 85), (312, 'Michelle Rogers', 85),
(162, 'Kelly Ripa', 87), (51, 'Fred Smith', 89),
(322, 'Alfred Roosevelt', 89), (481, 'Wayne Rosing', 91),
(261, 'Samuel Davis', 92), (621, 'Philip Wilson', 95),
(782, 'Jay Leno', 93), (361, 'Max Heindel', 97),
(891, 'Gerry Starr', 94);

This SQL statement should return a response of INSERT 0 13, indicatng that 13 records were inserted into the table.

Screenshot using psql to insert data into a table for a Postgres CASE example

Postgres ‘CASE WHEN’ example

Let’s imagine that the fictional students in our table attend a school that grants scholarships to the students whose grade is 90 and above. We can use CASE WHEN to select just the records with grades greater than or equal to 90:

1
2
3
4
5
6
7
SELECT name, grade,
CASE WHEN grade >= 90 THEN 'Can be granted scholarship'
     ELSE 'Cannot be granted'
END
AS aspiring_scholar
FROM student
ORDER BY aspiring_scholar;

The result of this query would be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
       name       | grade |      aspiring_scholar
------------------+-------+----------------------------
 Wayne Rosing     |    91 | Can be GRANTED scholarship
 Samuel Davis     |    92 | Can be GRANTED scholarship
 Philip Wilson    |    95 | Can be GRANTED scholarship
 Jay Leno         |    93 | Can be GRANTED scholarship
 MAX Heindel      |    97 | Can be GRANTED scholarship
 Gerry Starr      |    94 | Can be GRANTED scholarship
 Jerry Lawrel     |    92 | Can be GRANTED scholarship
 Alfred Roosevelt |    89 | Cannot be GRANTED
 Tommy Jones      |    85 | Cannot be GRANTED
 Michelle Rogers  |    85 | Cannot be GRANTED
 Kelly Ripa       |    87 | Cannot be GRANTED
 Fred Smith       |    89 | Cannot be GRANTED
 Allen James      |    89 | Cannot be GRANTED

Using the CASE statement, you can also obtain a sum and can compare data easily:

1
2
3
4
5
6
7
8
9
10
11
12
SELECT
SUM(
CASE WHEN grade >= 90 THEN 1
     ELSE 0
END
) AS scholars,
SUM(
CASE WHEN grade < 90 THEN 1
     ELSE 0
END
) AS non_scholars
FROM student ;

The result of the query above would look like this:

1
2
3
 scholars | non_scholars
----------+--------------
        7 |            6

As you can see, the CASE WHEN SQL statement is very useful for querying data and organizing the results in a manner that reflects the subtle differences in the records’ values.

Screenshot of a Postgres CASE WHEN example in the psql interface for PostgreSQL

Conclusion

Conditional expressions are a key component of programming logic, but they can also play a valuable role in database queries. In this article, we discussed the Postgres CASE statement and looked at some examples of how this conditional expression can be used in queries. With our examples and instructions to guide you, you’ll be able to incorporate the Postgres CASE statement into your own database queries.

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.