August 4, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL CASE conditional expression to form conditional queries.
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.
CASE is an expression, you can use it in any places where an expression can be used e.g.,
GROUP BY, and
CASE expression has two forms: general and simple form.
1) General PostgreSQL
The following illustrates the general form of the
CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 [WHEN ...] [ELSE else_result] END
In this syntax, each condition (
condition_2…) is a boolean expression that returns either
When a condition evaluates to
CASE expression evaluates the next condition from the top to bottom until it finds a condition that evaluates to
If a condition evaluates to
CASE expression returns the corresponding result that follows the condition. For example, if the
condition_2 evaluates to
CASE expression returns the
result_2. Also, it immediately stops evaluating the next expression.
In case all conditions evaluate to
CASE expression returns the result (
else_result) that follows the
ELSE keyword. If you omit the
ELSE clause, the
CASE expression returns
Let’s take a look at the
film table from the sample database.
A) A general CASE example
Suppose you want to label the films by their length based on the following logic:
- If the lengh is less than 50 minutes, the film is short.
- If the length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium.
- If the length is greater than 120 minutes, the film is long.
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;
Note that we placed a column alias
duration after the
B) Using CASE with an aggregate function example
Suppose that you want to assign price segments to films with the following logic:
- If the rental rate is 0.99, the film is economic.
- If the rental rate is 1.99, the film is mass.
- If the rental rate is 4.99, the film is premium.
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:
In this example, we used the
CASE expression to return 1 or 0 if the rental rate falls into each price segment. And we applied the
SUM function to calculate the total of films for each price segment.
2) Simple PostgreSQL
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 END
CASE first evaluates the
expression and compares the result with each value(
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
CASE does not find any matches, it returns the
else_result in that follows the
NULL value if the
ELSE is not available.
A) Simple PostgreSQL
CASE expression example
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;
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.
B) Using simple PostgreSQL
CASE expression with aggregate function example
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;
In this tutorial, you have learned how to use the PostgreSQL
CASE expression to form complex queries.