How to Use CASE in PostgreSQL

Shihab Sikder Feb 16, 2024
  1. How to Use the CASE Statement in PostgreSQL
  2. General Example of the CASE Statement in PostgreSQL
  3. PostgreSQL CASE Statement With Aggregate Functions
How to Use CASE in PostgreSQL

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:

  1. Age is 13 to 19, and height is between 140-160, then it’s Teens with average height.
  2. 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.

Shihab Sikder avatar Shihab Sikder avatar

I'm Shihab Sikder, a professional Backend Developer with experience in problem-solving and content writing. Building secure, scalable, and reliable backend architecture is my motive. I'm working with two companies as a part-time backend engineer.

LinkedIn Website