PostgreSQL CASE Expression

Summary: in this tutorial, you’ll learn how to use the PostgreSQL CASE expression to perform conditional logic within queries.

Getting Started with the PostgreSQL CASE Expression #

In PostgreSQL, a CASE expression is a powerful tool, allowing you to perform conditional logic within your queries.

The CASE expression works like an if-else statement in other programming languages.

Here’s the syntax of the CASE expression:

CASE  
   WHEN condition1 THEN result1
   WHEN condition2 THEN result2
   WHEN condition3 THEN result3
   ...
   ELSE result
ENDCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

Each condition (condition1, condition2, condition3 …) is a Boolean expression that returns true or false. The CASE expression evaluates conditions in sequence from top to bottom.

If a condition is true, the CASE expression returns the corresponding result in that follow the THEN and stops evaluating subsequent conditions.

For example, if condition1 is true, the CASE expression returns result1 and does not evaluate condition2 and condition3.

If no condition is true, the CASE expression returns the result of the ELSE clause. The ELSE clause is optional. If you omit it and no condition returns true, then the CASE expression returns NULL.

You can use the CASE expression within the queries where you use an expression. For example, you can use the CASE expression in the SELECT and WHERE clause of the SELECT statement.

PostgreSQL CASE expression example #

The following statement returns the product name and its total quantity in the inventory:

SELECT
  product_name,
  SUM(quantity) total_quantity
FROM
  inventories
  JOIN products USING (product_id)
GROUP BY
  product_name
ORDER BY
  total_quantity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | total_quantity
----------------------------+----------------
 Samsung Galaxy Z Fold 5    |            110
 Xiaomi Mi 14               |            120
 Sony Xperia 1 VI           |            130
 Apple iPhone 15 Pro Max    |            140
 Apple iPhone 15            |            150
 Samsung Galaxy Tab S9      |            160
 Apple iPad Pro 12.9        |            170
 Apple AirPods Pro 3        |            180
 Samsung Galaxy Buds Pro 2  |            190
...Code language: plaintext (plaintext)

To assign an inventory level to each product, you can use the CASE expression.

SELECT
  product_name,
  SUM(quantity) total_quantity,
  CASE
    WHEN SUM(quantity) <= 120 THEN 'Low'
    WHEN SUM(quantity) > 120  AND SUM(quantity) <= 150 THEN 'Medium'
    WHEN SUM(quantity) > 150 THEN 'High'
  END inventory_level
FROM
  inventories
  JOIN products USING (product_id)
GROUP BY
  product_name
ORDER BY
  total_quantity;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

        product_name        | total_quantity | inventory_level
----------------------------+----------------+-----------------
 Samsung Galaxy Z Fold 5    |            110 | Low
 Xiaomi Mi 14               |            120 | Low
 Sony Xperia 1 VI           |            130 | Medium
 Apple iPhone 15 Pro Max    |            140 | Medium
 Apple iPhone 15            |            150 | Medium
 Samsung Galaxy Tab S9      |            160 | High
 Apple iPad Pro 12.9        |            170 | High
 Apple AirPods Pro 3        |            180 | High
 Samsung Galaxy Buds Pro 2  |            190 | High
...Code language: plaintext (plaintext)

In this statement, we assign an inventory level to each product based on its total quantity.

If the total quantity is less than or equal to 120, its inventory level is low.

Its inventory level is medium if the total quantity is greater than 120 and less than or equal to 150.

If the total quantity exceeds 150, then the inventory is high.

Simple PostgreSQL CASE expression #

If you have one condition to evaluate, then you can use an alternative CASE expression:

CASE expression
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3
  ...
  ELSE result
ENDCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax, the CASE evaluates the expression, compares its result with value1, value2, value3 …, and returns the corresponding result.

If the result of the expression does not match any value, then the CASE expression returns the result in the ELSE clause.

The ELSE clause is optional. If you omit the ELSE clause and the expression does not match any value (value1, value2, value3, …), then the CASE expression returns NULL.

The following example uses the simple CASE expression to make the inventory transaction more clear:

SELECT
  transaction_id,
  type,
  CASE type
    WHEN 'issue' THEN 'Goods Issue'
    WHEN 'receipt' THEN 'Goods Receipt'
  END transaction_type
FROM
  transactions;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

 transaction_id |  type   | transaction_type
----------------+---------+------------------
              1 | issue   | Goods Issue
              2 | issue   | Goods Issue
              3 | issue   | Goods Issue
              4 | receipt | Goods Receipt
              5 | receipt | Goods Receipt
...Code language: plaintext (plaintext)

In this example, the CASE expression evaluates the values in the type column of the transactions table. If the value is 'issue' , it returns 'Goods Issue' . If the value is 'receipt', it returns ‘Goods Receipt‘.

Summary #

Use the CASE expression to add conditional logic to your queries.

Quiz #

Was this tutorial helpful ?