JIYIK CN >

This article shows how to use the statement in PostgreSQL CASE.

CASEHow to use the statement in PostgreSQL

caseStatements are similar to those in general-purpose programming languages if-else. But in SQL, if you want to write IF-ELSE, you probably need to PL/SQL.

In PostgreSQL, there is casebuilt-in functionality for using expressions.

For example, suppose 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 above table, following are the SQL commands.

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 fill the table; you can use 插入commands.

CASEGeneral examples of statements in PostgreSQL

For example, you are asked to divide the accounts according to height. If the height is less than 140 cm, then it is 矮; if it is between 140 and 160, then 平均and 160 or higher will be marked as 更高.

The general structure of a PostgreSQL caseexpression is as follows.

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 WHEN, we can use the usual conditions using the combination of ANDand . Therefore, the SQL command will be below.OR

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 ---

CASEPostgreSQL statements with aggregate functions

We can also CASEwrite more complex queries using expressions. For example, we want to divide the accounts into groups where:

1. Age 13-19, height 140-160, that's it Teens with average height.
2. Age 20-40, height between 165-175, that's it Adults with average height.

We want to calculate the total number of accounts under these two categories. The SQL command will be as follows.

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)

In each case, it returns 1 if the condition is met, otherwise it returns 0. The aggregate function then SUMadds up all the 1 or true conditions.

Count(*)Count the total rows in the Accounts table.

These types of expressions will help you in future to build custom aggregate functions where you can put your conditions accordingly.