Postgres Case Examples
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.
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.
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