How to Use CASE in PostgreSQL
-
How to Use the
CASEStatement in PostgreSQL -
General Example of the
CASEStatement in PostgreSQL -
PostgreSQL
CASEStatement With Aggregate Functions
This article shows how to use the CASE statement in PostgreSQL.
How to Use the CASE Statement in PostgreSQL
The case statement is similar to the if-else in the general programming language. But in SQL, if you want to write IF-ELSE, you might need PL/SQL.
In PostgreSQL, there’s built-in functionality to use the case expressions.
For example, let’s say you have the following database.
postgres=# \d accounts;
Table "public.accounts"
Column | Type | Collation | Nullable | Default
-----------+-----------------------------+-----------+----------+-------------------------------------------
user_id | integer | | not null | nextval('accounts_user_id_seq'::regclass)
username | character varying(50) | | not null |
password | character varying(50) | | not null |
email | character varying(255) | | not null |
contact | character varying(20) | | |
postcode | integer | | |
age | integer | | |
height | integer | | |
timestamp | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
"accounts_pkey" PRIMARY KEY, btree (user_id)
"accounts_email_key" UNIQUE CONSTRAINT, btree (email)
"accounts_username_key" UNIQUE CONSTRAINT, btree (username)
If you want to create the table above, the SQL command is below.
CREATE TABLE accounts (
user_id serial PRIMARY KEY,
username VARCHAR ( 50 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
contact VARCHAR (20),
postcode INT,
age INT,
height INT,
timestamp timestamp default current_timestamp
);
After that, use a script to populate the table; you can use the insert command.
General Example of the CASE Statement in PostgreSQL
For example, you’ve been asked to divide the accounts based on their height. If the height is less than 140cm, it’s short; if it’s between 140 and 160, then average and 160 or above will be marked as taller.
PostgreSQL’s general structure of case expression is given below.
SELECT <col1>...
CASE
WHEN <condition_1> THEN <result_1>
WHEN <condition_2> THEN <result_2>
....
...
WHEN <condition> THEN <result>
ELSE <else_result>
END <column_name_of_result>
FROM <table_name>
In the WHEN, we can use the usual condition with a combination of AND and OR. So, the SQL command will be below.
SELECT username, email, height,
CASE
WHEN height < 140 THEN 'SHORT'
WHEN height >140 AND height<160 THEN 'AVERAGE'
ELSE 'TALLER'
END height_group
FROM accounts;
Output:
username | email | height | height_group
----------+------------------+--------+--------------
RNYAvQR | xgsgla@gmail.com | 150 | AVERAGE
djIlNbP | gfrqiy@gmail.com | 188 | TALLER
vKUEtyK | lffemk@gmail.com | 160 | TALLER
OkGRtRA | niwjri@gmail.com | 190 | TALLER
jRnoRDI | kofdcl@gmail.com | 163 | TALLER
czkarSx | qbqhyh@gmail.com | 186 | TALLER
GGFcCrz | tcbkip@gmail.com | 193 | TALLER
cfVgeZE | vpodqb@gmail.com | 161 | TALLER
XRivfYx | ldnnfg@gmail.com | 134 | SHORT
WUKNQYe | kvirum@gmail.com | 200 | TALLER
lwQODTo | apqvgc@gmail.com | 160 | TALLER
eYyvVNu | tsrioo@gmail.com | 190 | TALLER
CLFFGcF | nlpiuu@gmail.com | 131 | SHORT
VhxqxTl | hwrzao@gmail.com | 173 | TALLER
---- more ---
PostgreSQL CASE Statement With Aggregate Functions
We can also write a more complex query with the CASE expression. For example, we want to divide the accounts into such a group where:
- Age is 13 to 19, and height is between 140-160, then it’s
Teens with average height. - Age is 20 to 40, and height is between 165-175, then it’s
Adults with average height.
We want to count the total number of accounts under these two categories. The SQL command will be the following.
SELECT
SUM(
CASE
WHEN age>=13 AND age<=19 THEN 1
ELSE 0
END
) AS "Teens with average height",
SUM(
CASE
WHEN age>=20 AND age<=40 THEN 1
ELSE 0
END
) AS "Adults with average height",
COUNT(*) as total_accounts
FROM
accounts;
Output:
Teens with average height | Adults with average height | total_accounts
---------------------------+----------------------------+----------------
27 | 84 | 200
(1 row)
Inside each case, if the condition is met, it returns 1 else 0. Then the aggregate function SUM adds up all the 1 or true conditions.
Count(*) counts the total rows in the account table.
These types of expressions will help you build custom aggregate functions in the future, where you can put your conditions accordingly. You can learn more from here.
