SinceCASE is an expression, you can use it in any place where you would use an expression such asSELECT,WHERE,GROUP BY, andHAVING clauses.

TheCASE expression has two forms:

• General
• Simple

General PostgreSQL CASE expression

The following illustrates the general form of theCASE statement:

CASE      WHEN condition_1  THEN result_1      WHEN condition_2  THEN result_2      [WHEN ...]      [ELSE else_result]END

In this syntax, each condition (condition_1,condition_2…) is a boolean expression that returns eithertrue orfalse.

When a condition evaluates tofalse, theCASE expression evaluates the next condition from top to bottom until it finds a condition that evaluates totrue.

If a condition evaluates totrue, theCASE expression returns the corresponding result that follows the condition.

For example, if thecondition_2 evaluates totrue, theCASE expression returns theresult_2. Also, it immediately stops evaluating the remaining expressions.

If all conditions arefalse, theCASE expression returns the result (else_result) that follows theELSE keyword. If you omit theELSE clause, theCASE expression returnsNULL.

1) The general CASE example

Let’s take a look at thefilm table from thesample database.

Suppose you want to label the films by their lengths based on the following logic:

• If the length is less than 50 minutes, the film is short.
• If the length is greater than 50 minutes and less than or equal to 120 minutes, the film is medium.
• If the length is greater than 120 minutes, the film is long.

To apply this logic, you can use theCASE expression in theSELECT statement as follows:

SELECT  title,  length,  CASE WHEN length > 0  AND length <= 50 THEN 'Short' WHEN length > 50  AND length <= 120 THEN 'Medium' WHEN length > 120 THEN 'Long' END durationFROM  filmORDER BY  title;

Output:

title| length| duration-----------------------------+--------+---------- Academy Dinosaur|     86 | Medium Ace Goldfinger|     48 | Short Adaptation Holes|     50 | Short Affair Prejudice|    117 | Medium African Egg|    130 | Long Agent Truman|    169 | Long

Note that we placed acolumn aliasduration after theCASE expression.

2) Using CASE with an aggregate function example

Suppose that you want to assign price segments to films with the following logic:

• If the rental rate is 0.99, the film is economic.
• If the rental rate is 1.99, the film is mass.
• If the rental rate is 4.99, the film is premium.

And you want to know the number of films that belong to economy, mass, and premium.

In this case, you can use theCASE expression to construct the query as follows:

SELECT  SUM (    CASE WHEN rental_rate = 0.99 THEN 1 ELSE 0 END  ) AS"Economy",  SUM (    CASE WHEN rental_rate = 2.99 THEN 1 ELSE 0 END  ) AS"Mass",  SUM (    CASE WHEN rental_rate = 4.99 THEN 1 ELSE 0 END  ) AS"Premium"FROM  film;

The result of the query is as follows:

Economy | Mass | Premium---------+------+---------     341 |  323 |     336(1 row)

In this example, we used theCASE expression to return 1 or 0 if the rental rate falls into each price segment. We applied theSUM function to calculate the total of films for each price segment.

Simple PostgreSQL CASE expression

PostgreSQL provides another form of theCASE expression called simple form as follows:

CASE expression   WHEN value_1 THEN result_1   WHEN value_2 THEN result_2   [WHEN ...]ELSE   else_resultEND

TheCASE first evaluates theexpression and compares the result with each value(value_1,value_2, …) in theWHEN clauses sequentially until it finds the match.

Once the result of theexpression equals a value (value1, value2, etc.) in aWHEN clause, theCASE returns the corresponding result in theTHEN clause.

IfCASE does not find any matches, it returns theelse_result in that follows theELSE, orNULL value if theELSE is not available.

1) Simple PostgreSQL CASE expression example

The following statement uses theCASE expression to add the rating description to the output:

SELECT title,       rating,       CASE rating           WHEN 'G' THEN 'General Audiences'           WHEN 'PG' THEN 'Parental Guidance Suggested'           WHEN 'PG-13' THEN 'Parents Strongly Cautioned'           WHEN 'R' THEN 'Restricted'           WHEN 'NC-17' THEN 'Adults Only'       END rating_descriptionFROM filmORDER BY title;

Output:

title| rating|     rating_description-----------------------------+--------+----------------------------- Academy Dinosaur| PG| Parental Guidance Suggested Ace Goldfinger| G| General Audiences Adaptation Holes| NC-17  | Adults Only Affair Prejudice| G| General Audiences African Egg| G| General Audiences Agent Truman| PG| Parental Guidance Suggested Airplane Sierra| PG-13  | Parents Strongly Cautioned...

In this example, we used a simpleCASE expression to compare the rating from thefilm table with some literal values like G, PG, NC17, PG-13 and return the corresponding rating description.

2) Using simple PostgreSQL CASE expression with aggregate function example

The following statement usesCASE expression with theSUM function to calculate the number of films in each rating:

SELECT  SUM(CASE rating WHEN 'G' THEN 1 ELSE 0 END)"General Audiences",  SUM(    CASE rating WHEN 'PG' THEN 1 ELSE 0 END  )"Parental Guidance Suggested",  SUM(    CASE rating WHEN 'PG-13' THEN 1 ELSE 0 END  )"Parents Strongly Cautioned",  SUM(CASE rating WHEN 'R' THEN 1 ELSE 0 END)"Restricted",  SUM(    CASE rating WHEN 'NC-17' THEN 1 ELSE 0 END  )"Adults Only"FROM  film;

Output:

General Audiences | Parental Guidance Suggested | Parents Strongly Cautioned | Restricted | Adults Only-------------------+-----------------------------+----------------------------+------------+-------------               178 |                         194 |                        223 |        195 |         210(1 row)

In this tutorial, you have learned how to use the PostgreSQLCASE expression to form complex queries.

Last updated onFebruary 1, 2024

On this page

• General PostgreSQL CASE expression
• Simple PostgreSQL CASE expression