PostgreSQL CASE conditional expressions

A PostgreSQL CASE expression is a conditional expression that works the same as an if-else statement in other programming languages.

You can use CASE expressions in SELECT statements as well as WHERE, GROUP BY, and HAVING clauses.

PostgreSQL CASE syntax

Here is the syntax of the PostgreSQL CASE expression:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE value3
END

Explanation:

• The condition1, condition2 are boolean expressions and return true or false.
• This expression can be understood as: if condition1 is true return result1, if condition2 is true return result2, otherwise return result3.
• You can specify one or more WHEN ... THEN case.
• The ELSE part can be omitted, you can only specify one ELSE statement.
• All conditions are test from top to bottom until the condition is true, and the value corresponding to this condition is returned. Otherwise it returns the value specified in ELSE clause. Returns NULL if ELSE not specified .

If all conditions are equivalent to the same field or expression, the CASE expression can be abbreviated as follows:

CASE expr
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE result3
END

The meaning of this expression is: if expr equals value1, return result1, if expr equals value2, return result2, otherwise return result3.

PostgreSQL CASE Examples

Simple example

Let’s start with a simple example.

For example, you want to get the name of the day of the week that today is, use the following statement:

SELECT
  current_date "Today",
  CASE
    WHEN extract(DOW FROM current_date) = 1 THEN 'Monday'
    WHEN extract(DOW FROM current_date) = 2 THEN 'Tuesday'
    WHEN extract(DOW FROM current_date) = 3 THEN 'Wednesday'
    WHEN extract(DOW FROM current_date) = 4 THEN 'Thursday'
    WHEN extract(DOW FROM current_date) = 5 THEN 'Friday'
    WHEN extract(DOW FROM current_date) = 6 THEN 'Saturday'
    ELSE 'Sunday'
  END "Day of Week";

  Today    | Day of Week
------------+-------------
2022-09-19 | Monday

Here, we use current_date to get the current date, and use the extract() function to get the weekday number of the current date.

Since the conditions in the above CASE expression are all equality judgments, we can simplify the CASE expression to the following statement:

SELECT
  current_date "Today",
  CASE extract(DOW FROM current_date)
    WHEN 1 THEN 'Monday'
    WHEN 2 THEN 'Tuesday'
    WHEN 3 THEN 'Wednesday'
    WHEN 4 THEN 'Thursday'
    WHEN 5 THEN 'Friday'
    WHEN 6 THEN 'Saturday'
    ELSE 'Sunday'
  END "Day of Week";

  Today    | Day of Week
------------+-------------
2022-09-19 | Monday

Use CASE expressions to implement custom sorting

Sometimes simply sorting by the value of the field does not meet the requirements, we need to sort in a custom order. For example, we need to sort films according to the rating 'G', 'PG', 'PG-13', 'R', 'NC-17'.

For such a requirement, it can be understood as sorting according to the index position of the elements in the rating list. We implement it using the CASE expression.

In the following examples, we use the film table for demonstration.

Suppose you want to sort films according to their ratings in the order of 'G', 'PG', 'PG-13', 'R', 'NC-17'. The following uses CASE expressions to implement custom sorting:

SELECT
    film_id, title, rating
FROM
    film
ORDER BY CASE rating
    WHEN 'G' THEN 1
    WHEN 'PG' THEN 2
    WHEN 'PG-13' THEN 3
    WHEN 'R' THEN 4
    WHEN 'NC-17' THEN 5
END;

    357 | GILBERT PELICAN             | G
    597 | MOONWALKER FOOL             | G
    354 | GHOST GROUNDHOG             | G
...
    595 | MOON BUNCH                  | PG
      6 | AGENT TRUMAN                | PG
    600 | MOTIONS DETAILS             | PG
...
      9 | ALABAMA DEVIL               | PG-13
    657 | PARADISE SABRINA            | PG-13
    956 | WANDA CHAMBER               | PG-13
...
    749 | RULES HUMAN                 | R
      8 | AIRPORT POLLOCK             | R
     17 | ALONE TRIP                  | R
...
    520 | LICENSE WEEKEND             | NC-17
    517 | LESSON CLEOPATRA            | NC-17
    114 | CAMELOT VACATION            | NC-17
...
(1000 rows)

In this example, we use the CASE expression to convert the movie’s rating to a number. Then ORDER BY use this number to sort rows from the film table.

Conclusion

In this article, we discussed the syntax of PostgreSQL CASE conditional expressions and provided several examples.