{"id":701,"date":"2024-12-07T10:17:43","date_gmt":"2024-12-07T03:17:43","guid":{"rendered":"https:\/\/www.pgtutorial.com\/?page_id=701"},"modified":"2025-01-02T20:43:29","modified_gmt":"2025-01-02T13:43:29","slug":"postgresql-case","status":"publish","type":"page","link":"https:\/\/www.pgtutorial.com\/postgresql-tutorial\/postgresql-case\/","title":{"rendered":"PostgreSQL CASE Expression"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PostgreSQL CASE<\/code> expression to perform conditional logic within queries.<\/p>\n\n\n\n

Getting Started with the PostgreSQL CASE Expression #<\/a><\/h2>\n\n\n\n

In PostgreSQL, a CASE<\/code> expression is a powerful tool, allowing you to perform conditional logic within your queries<\/a>.<\/p>\n\n\n\n

The CASE<\/code> expression works like an if-else statement in other programming languages.<\/p>\n\n\n\n

Here’s the syntax of the CASE<\/code> expression:<\/p>\n\n\n

CASE<\/span>\u00a0\u00a0\n   WHEN<\/span> condition1 THEN<\/span> result1\n   WHEN<\/span> condition2 THEN<\/span> result2\n   WHEN<\/span> condition3 THEN<\/span> result3\n   ...\n   ELSE<\/span> result\nEND<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
If a condition is true, the CASE<\/code> expression returns the corresponding result in that follow the THEN<\/code>  and stops evaluating subsequent conditions.<\/p>\n\n\n\n
For example, if condition1<\/code> is true, the CASE<\/code> expression returns result1<\/code> and does not evaluate condition2<\/code> and condition3<\/code>.<\/p>\n\n\n\n
If no condition is true<\/code>, the CASE<\/code> expression returns the result<\/code> of the ELSE<\/code> clause. The ELSE<\/code> clause is optional. If you omit it and no condition returns true, then the CASE<\/code> expression returns NULL<\/code>.<\/p>\n\n\n\n
You can use the CASE<\/code> expression within the queries where you use an expression. For example, you can use the CASE<\/code> expression in the SELECT<\/a><\/code><\/code> and WHERE<\/a><\/code> clause of the SELECT<\/code><\/code> statement.<\/p>\n\n\n\n
PostgreSQL CASE expression example #<\/a><\/h2>\n\n\n\n
The following statement returns the product name and its total quantity in the inventory:<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  SUM(quantity) total_quantity\nFROM<\/span>\n  inventories\n  JOIN<\/span> products USING<\/span> (product_id)\nGROUP<\/span> BY<\/span>\n  product_name\nORDER<\/span> BY<\/span>\n  total_quantity;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        product_name        | total_quantity\n----------------------------+----------------\n Samsung Galaxy Z Fold 5    |            110\n Xiaomi Mi 14               |            120\n Sony Xperia 1 VI           |            130\n Apple iPhone 15 Pro Max    |            140\n Apple iPhone 15            |            150\n Samsung Galaxy Tab S9      |            160\n Apple iPad Pro 12.9        |            170\n Apple AirPods Pro 3        |            180\n Samsung Galaxy Buds Pro 2  |            190\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
To assign an inventory level to each product, you can use the CASE<\/code> expression.<\/p>\n\n\n
SELECT<\/span>\n  product_name,\n  SUM(quantity) total_quantity,\n  CASE<\/span>\n    WHEN<\/span> SUM(quantity) <= 120<\/span> THEN<\/span> 'Low'<\/span>\n    WHEN<\/span> SUM(quantity) > 120<\/span>  AND<\/span> SUM(quantity) <= 150<\/span> THEN<\/span> 'Medium'<\/span>\n    WHEN<\/span> SUM(quantity) > 150<\/span> THEN<\/span> 'High'<\/span>\n  END<\/span> inventory_level\nFROM<\/span>\n  inventories\n  JOIN<\/span> products USING<\/span> (product_id)\nGROUP<\/span> BY<\/span>\n  product_name\nORDER<\/span> BY<\/span>\n  total_quantity;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
        product_name        | total_quantity | inventory_level\n----------------------------+----------------+-----------------\n Samsung Galaxy Z Fold 5    |            110 | Low\n Xiaomi Mi 14               |            120 | Low\n Sony Xperia 1 VI           |            130 | Medium\n Apple iPhone 15 Pro Max    |            140 | Medium\n Apple iPhone 15            |            150 | Medium\n Samsung Galaxy Tab S9      |            160 | High\n Apple iPad Pro 12.9        |            170 | High\n Apple AirPods Pro 3        |            180 | High\n Samsung Galaxy Buds Pro 2  |            190 | High\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this statement, we assign an inventory level to each product based on its total quantity.<\/p>\n\n\n\n
If the total quantity is less than or equal to 120, its inventory level is low.<\/p>\n\n\n\n
Its inventory level is medium if the total quantity is greater than 120 and less than or equal to 150.<\/p>\n\n\n\n
If the total quantity exceeds 150, then the inventory is high.<\/p>\n\n\n\n
Simple PostgreSQL CASE expression #<\/a><\/h2>\n\n\n\n
If you have one condition to evaluate, then you can use an alternative CASE<\/code> expression:<\/p>\n\n\n
CASE<\/span> expression\n  WHEN<\/span> value1 THEN<\/span> result1\n  WHEN<\/span> value2 THEN<\/span> result2\n  WHEN<\/span> value3 THEN<\/span> result3\n  ...\n  ELSE<\/span> result\nEND<\/span><\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the CASE<\/code> evaluates the expression, compares its result with value1<\/code>, value2<\/code>, value3<\/code> …, and returns the corresponding result.<\/p>\n\n\n\n
If the result of the expression does not match any value, then the CASE<\/code> expression returns the result<\/code> in the ELSE<\/code> clause.<\/p>\n\n\n\n
The ELSE<\/code><\/code> clause is optional. If you omit the ELSE<\/code><\/code> clause and the expression does not match any value (value1<\/code>, value2<\/code>, value3<\/code>, …), then the CASE<\/code> expression returns NULL<\/code>.<\/p>\n\n\n\n
The following example uses the simple CASE<\/code> expression to make the inventory transaction more clear:<\/p>\n\n\n
SELECT<\/span>\n  transaction_id,\n  type<\/span>,\n  CASE<\/span> type<\/span>\n    WHEN<\/span> 'issue'<\/span> THEN<\/span> 'Goods Issue'<\/span>\n    WHEN<\/span> 'receipt'<\/span> THEN<\/span> 'Goods Receipt'<\/span>\n  END<\/span> transaction_type\nFROM<\/span>\n  transactions;<\/code><\/span>Code language:<\/span> PostgreSQL SQL dialect and PL\/pgSQL<\/span> (<\/span>pgsql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 transaction_id |  type   | transaction_type\n----------------+---------+------------------\n              1 | issue   | Goods Issue\n              2 | issue   | Goods Issue\n              3 | issue   | Goods Issue\n              4 | receipt | Goods Receipt\n              5 | receipt | Goods Receipt\n...<\/code><\/span>Code language:<\/span> plaintext<\/span> (<\/span>plaintext<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example, the CASE<\/code> expression evaluates the values in the type<\/code> column of the transactions<\/code> table. If the value is 'issue'<\/code> , it returns 'Goods Issue'<\/code> . If the value<\/code> is 'receipt'<\/code>, it returns ‘Goods Receipt<\/code>‘.<\/p>\n\n\n\n
Summary #<\/a><\/h2>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n
Each condition (condition1<\/code>, condition2<\/code>, condition3 <\/code>…) is a Boolean<\/a> expression that returns true<\/code> or false<\/code>. The CASE<\/code> expression evaluates conditions in sequence from top to bottom.<\/p>\n\n\n\n