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.
Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.