PostgreSQL CASE Expression

In PostgreSQL, the CASE expression compares a list of conditions and returns one of multiple possible result expressions.

The CASE expression is included in the SQL standard (ISO/IEC 9075), and most major RDBMSs support it.

Syntax

The basic syntax for the CASE expression goes like this:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

Actually, there are two forms of CASE expression. One form is the simple CASE expression:

CASE value 
    WHEN compare_value THEN result 
    [WHEN compare_value THEN result ...] 
    [ELSE result] 
END

The simple CASE expression compares an expression to a set of simple expressions to determine the result.

The other option is the searched CASE expression:

CASE 
    WHEN condition THEN result 
    [WHEN condition THEN result ...] 
    [ELSE result] 
END

The searched CASE expression evaluates a set of Boolean expressions to determine the result.

Example

Here’s an example to demonstrate the simple CASE expression:

SELECT
    genreid,
    CASE genreid
        WHEN 1 THEN 'Rock'
        WHEN 2 THEN 'Jazz'
        WHEN 3 THEN 'Country'
        WHEN 4 THEN 'Pop'
        WHEN 5 THEN 'Blues'
        ELSE 'Other'
    END AS genre
FROM public.genres;

Result:

 genreid |  genre  
---------+---------
       1 | Rock
       2 | Jazz
       3 | Country
       4 | Pop
       5 | Blues
       6 | Other
       7 | Other
       8 | Other

We could rewrite this to a searched CASE expression if we wanted. We could do that like this:

SELECT
    genreid,
    CASE 
        WHEN genreid = 1 THEN 'Rock'
        WHEN genreid = 2 THEN 'Jazz'
        WHEN genreid = 3 THEN 'Country'
        WHEN genreid = 4 THEN 'Pop'
        WHEN genreid = 5 THEN 'Blues'
        ELSE 'Other'
    END AS genre
FROM public.genres;

Result:

 genreid |  genre  
---------+---------
       1 | Rock
       2 | Jazz
       3 | Country
       4 | Pop
       5 | Blues
       6 | Other
       7 | Other
       8 | Other

Same result.

The Optional ELSE Clause

The above examples use the ELSE clause to determine what happens if none of the conditions are met, however, the ELSE clause is optional.

If we omit the ELSE clause, and none of the conditions are met, the result is NULL.

Let’s omit the ELSE clause from the first example:

SELECT
    genreid,
    CASE genreid
        WHEN 1 THEN 'Rock'
        WHEN 2 THEN 'Jazz'
        WHEN 3 THEN 'Country'
        WHEN 4 THEN 'Pop'
        WHEN 5 THEN 'Blues'
    END AS genre
FROM public.genres;

Result:

 genreid |  genre  
---------+---------
       1 | Rock
       2 | Jazz
       3 | Country
       4 | Pop
       5 | Blues
       6 | <null>
       7 | <null>
       8 | <null>

In my case, NULL values are represented by the string <null>. That’s because I set up psql to return that string for NULL values.

Using the CASE Operator in Other Contexts

The CASE operator isn’t limited to just SELECT statements. We can use it pretty much anywhere a valid expression can be used.

For example, we can use the CASE operator as part of an UPDATE statement when updating data in a database. See SQL CASE Statement for examples.