CASE clause

Description

A CASE clause is a generic conditional expression. Conditions are written through the constructs WHEN …​ THEN.

Syntax

CASE
    WHEN <condition1> THEN <result1>
  [ WHEN <condition2> THEN <result2> ]
  [ ... ]
  [ ELSE <result3> ]
END
CASE <expression>
    WHEN <value1> THEN <result1>
  [ WHEN <value2> THEN <result2> ]
  [ ... ]
  [ ELSE <result3> ]
END

Parameters

  • <condition>
    Specifies the condition — expression to evaluate to a boolean value (True, False, or NULL).

  • <expression>
    Generic expression.

  • <value>
    The value whose match is checked for the expression <expression>. The value can be a literal or an expression. The value must have the same data type as <expression> or have a data type that can be converted to the <expression> data type.

  • <result>
    The result that the expression returns. If one condition is true, the corresponding result is returned. If more than one condition is true, the result given by the first true condition is returned. If the optional ELSE operator is present and no match is found in WHEN, the expression returns the result by ELSE. If the ELSE operator is absent and no match is found in WHEN, the result is NULL.

Examples

  • Let’s create a table of numbers from 1 to 10. In the result column we will write the result of comparing the numbers with the number 5.

    CREATE TABLE demo.numbers AS
    SELECT unnest(generate_series(1,10)) AS number;

SELECT
    number,
    CASE
        WHEN number < 5 THEN 'less than 5'
        WHEN number > 5 THEN 'more than 5'
        ELSE 'equal to 5'
    END AS result
FROM demo.numbers;
    +--------+-------------+
| number | result      |
+--------+-------------+
| 1      | less than 5 |
+--------+-------------+
| 2      | less than 5 |
+--------+-------------+
| 3      | less than 5 |
+--------+-------------+
| 4      | less than 5 |
+--------+-------------+
| 5      | equal to 5  |
+--------+-------------+
| 6      | more than 5 |
+--------+-------------+
| 7      | more than 5 |
+--------+-------------+
| 8      | more than 5 |
+--------+-------------+
| 9      | more than 5 |
+--------+-------------+
| 10     | more than 5 |
+--------+-------------+

  • Create a table of cities with countries and populations. Calculate the average population value for cities from Russia and the maximum value for cities from Spain:

    CREATE TABLE demo.cities(city_name VARCHAR, city_country VARCHAR, city_population BIGINT);

INSERT INTO demo.cities VALUES
    ('Moscow', 'Russia', 13000000),
    ('Saint Petersburg', 'Russia', 5600000),
    ('Madrid', 'Spain', 3400000),
    ('Barcelona', 'Spain', 1700000);

SELECT
    avg(CASE WHEN city_country == 'Russia' THEN city_population END)
    AS "Russia avg city population",
    max(CASE WHEN city_country == 'Spain' THEN city_population END)
    AS "Spain max city population"
FROM demo.cities;
    +----------------------------+---------------------------+
| russia avg city population | spain max city population |
+----------------------------+---------------------------+
| 9300000                    | 3400000                   |
+----------------------------+---------------------------+