PostgreSQL - CASE

In PostgreSQL, the CASE expression allows you to perform conditional operations within your SQL queries. It evaluates a list of conditions and returns a result when the first condition is met. If no conditions are met, it returns the result specified in the ELSE clause.

Let us better understand the CASE Statement in PostgreSQL to better understand the concept.

Syntax

1. General Form

The general form of the CASE expression evaluates conditions and returns the corresponding result:

CASEWHEN condition_1 THEN result_1
 WHEN condition_2 THEN result_2
 [WHEN ...]
 [ELSE result_n]
END

2. Simple Form

The simple form of the CASE expression compares an expression to a set of values and returns the corresponding result:

CASE expression
 WHEN value_1 THEN result_1
 WHEN value_2 THEN result_2 
 [WHEN ...]
 ELSE result_n
END;

PostgreSQL CASE Statement Examples

For examples we will be using the sample database (ie, dvdrental).

Example 1: General CASE Expression

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.

Query:

SELECTSUM (
 CASEWHEN rental_rate = 0.99 THEN 1
 ELSE 0
 END
 ) AS "Mass",
 SUM (
 CASEWHEN rental_rate = 2.99 THEN 1
 ELSE 0
 END
 ) AS "Economic",
 SUM (
 CASEWHEN rental_rate = 4.99 THEN 1
 ELSE 0
 END
 ) AS "Luxury"
FROM
 film;

Output: This query will return the number of films in each price segment.

Example 2: Simple CASE Expression

PostgreSQL provides another form of the CASE expression called simple form as follows.

Query:

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:

SELECTSUM (
 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.

Important Points About PostgreSQL CASE

• You can combine multiple conditions in the CASE expression using logical operators (AND, OR) for more complex logic.
• CASE expressions can be nested within each other to handle more intricate conditions and outcomes.
• PostgreSQL handles NULLs uniquely, and you might need to use functions like COALESCE or IS NULL in your CASE expressions.
• Complex CASE expressions can lead to performance issues, especially on large datasets. It's essential to test and optimize queries using EXPLAIN and other performance tuning tools in PostgreSQL.