PostgreSQL has a conditional expression called CASE to form conditional queries. The PostgreSQL CASE expression is the same as IF/ELSE statement in other programming languages. PostgreSQL provides two forms of the CASE expressions.
Syntax: CASE WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 [WHEN ...] [ELSE result_n] END
For examples we will be using the sample database (ie, dvdrental).
Example 1:
Here we will work on the film table of the sample database. Suppose you want to assign a price segment to a film with the following logic:
- Mass if the rental rate is 0.99
- Economic if the rental rate is 1.99
- Luxury if the rental rate is 4.99
We will query for number of films in each segment using the below statement:
SELECT SUM ( CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 END ) AS "Mass", SUM ( CASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 END ) AS "Economic", SUM ( CASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 END ) AS "Luxury" FROM film;
Output:
Example 2:
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 result_n END;
We can rewrite the general CASE expression using the simple CASE as follows:
SELECT SUM ( CASE rental_rate WHEN 0.99 THEN 1 ELSE 0 END ) AS "Mass", SUM ( CASE rental_rate WHEN 2.99 THEN 1 ELSE 0 END ) AS "Economic", SUM ( CASE rental_rate WHEN 4.99 THEN 1 ELSE 0 END ) AS "Luxury" FROM film;
Output:
The query returns the same result as the first CASE example.