PostgreSQL CASE (original) (raw)

Last Updated : 15 Jul, 2025

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:

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.

PostgreSQL CASE Example

**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.

PostgreSQL CASE Statement Example

Important Points About PostgreSQL CASE