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:

CASE 
      WHEN 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:

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: 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:

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.

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.