Summary: in this tutorial, you will learn how to use the PostgreSQL CASE conditional expression to form conditional queries.
The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. It allows you to add if-else logic to the query to form a powerful query.
Since CASE is an expression, you can use it in any place where you would use an expression such as SELECT, WHERE, GROUP BY, and HAVING clauses.
The CASE expression has two forms:
The following illustrates the general form of the CASE statement:
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
[WHEN ...]
[ELSE else_result]
ENDIn this syntax, each condition (condition_1, condition_2…) is a boolean expression that returns either true or false.
When a condition evaluates to false, the CASE expression evaluates the next condition from top to bottom until it finds a condition that evaluates to true.
If a condition evaluates to true, the CASE expression returns the corresponding result that follows the condition.
For example, if the condition_2 evaluates to true, the CASE expression returns the result_2. Also, it immediately stops evaluating the remaining expressions.
If all conditions are false, the CASE expression returns the result (else_result) that follows the ELSE keyword. If you omit the ELSE clause, the CASE expression returns NULL.
Let’s take a look at the film table from the sample database.
Suppose you want to label the films by their lengths based on the following logic:
To apply this logic, you can use the CASE expression in the SELECT statement as follows:
SELECT
title,
length,
CASE WHEN length > 0
AND length <= 50 THEN 'Short' WHEN length > 50
AND length <= 120 THEN 'Medium' WHEN length > 120 THEN 'Long' END duration
FROM
film
ORDER BY
title;Output:
title | length | duration
-----------------------------+--------+----------
Academy Dinosaur | 86 | Medium
Ace Goldfinger | 48 | Short
Adaptation Holes | 50 | Short
Affair Prejudice | 117 | Medium
African Egg | 130 | Long
Agent Truman | 169 | LongNote that we placed a column alias duration after the CASE expression.
Suppose that you want to assign price segments to films with the following logic:
And you want to know the number of films that belong to economy, mass, and premium.
In this case, you can use the CASE expression to construct the query as follows:
SELECT
SUM (
CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 END
) AS "Economy",
SUM (
CASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 END
) AS "Mass",
SUM (
CASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 END
) AS "Premium"
FROM
film;The result of the query is as follows:
Economy | Mass | Premium
---------+------+---------
341 | 323 | 336
(1 row)In this example, we used the CASE expression to return 1 or 0 if the rental rate falls into each price segment. We applied the SUM function to calculate the total of films for each price segment.
PostgreSQL provides another form of the CASE expression called simple form as follows:
CASE expression
WHEN value_1 THEN result_1
WHEN value_2 THEN result_2
[WHEN ...]
ELSE
else_result
ENDThe CASE first evaluates the expression and compares the result with each value( value_1, value_2, …) in the WHEN clauses sequentially until it finds the match.
Once the result of the expression equals a value (value1, value2, etc.) in a WHEN clause, the CASE returns the corresponding result in the THEN clause.
If CASE does not find any matches, it returns the else_result in that follows the ELSE, or NULL value if the ELSE is not available.
The following statement uses the CASE expression to add the rating description to the output:
SELECT title,
rating,
CASE rating
WHEN 'G' THEN 'General Audiences'
WHEN 'PG' THEN 'Parental Guidance Suggested'
WHEN 'PG-13' THEN 'Parents Strongly Cautioned'
WHEN 'R' THEN 'Restricted'
WHEN 'NC-17' THEN 'Adults Only'
END rating_description
FROM film
ORDER BY title;Output:
title | rating | rating_description
-----------------------------+--------+-----------------------------
Academy Dinosaur | PG | Parental Guidance Suggested
Ace Goldfinger | G | General Audiences
Adaptation Holes | NC-17 | Adults Only
Affair Prejudice | G | General Audiences
African Egg | G | General Audiences
Agent Truman | PG | Parental Guidance Suggested
Airplane Sierra | PG-13 | Parents Strongly Cautioned
...In this example, we used a simple CASE expression to compare the rating from the film table with some literal values like G, PG, NC17, PG-13 and return the corresponding rating description.
The following statement uses CASE expression with the SUM function to calculate the number of films in each rating:
SELECT
SUM(CASE rating WHEN 'G' THEN 1 ELSE 0 END) "General Audiences",
SUM(
CASE rating WHEN 'PG' THEN 1 ELSE 0 END
) "Parental Guidance Suggested",
SUM(
CASE rating WHEN 'PG-13' THEN 1 ELSE 0 END
) "Parents Strongly Cautioned",
SUM(CASE rating WHEN 'R' THEN 1 ELSE 0 END) "Restricted",
SUM(
CASE rating WHEN 'NC-17' THEN 1 ELSE 0 END
) "Adults Only"
FROM
film;Output:
General Audiences | Parental Guidance Suggested | Parents Strongly Cautioned | Restricted | Adults Only
-------------------+-----------------------------+----------------------------+------------+-------------
178 | 194 | 223 | 195 | 210
(1 row)In this tutorial, you have learned how to use the PostgreSQL CASE expression to form complex queries.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4