Using Case Expressions

In order to achieve simple programmatic transformations without having to call out to a procedural language, PostgreSQL supports standard SQL case expressions . These use the SQL keywords CASE, WHEN, THEN, and END to allow basic conditional transformations per each row.

The entirety of a case expression is syntactically placed within the SELECT statement's target list. A case expression's result column is named case by default, but it may be aliased in the same manner as any normal target list. The general syntax for a case expression in a SELECT statement's target list is as follows:

  CASE WHEN 
condition1
 THEN 
result1

       WHEN 
condition2
 THEN 
result2

       [ ... ]
       [ ELSE 
default_result
 ]
  END [ AS 
alias
 ]

The CASE, WHEN, THEN, and ELSE keywords are somewhat similar to the if-then-else logic in programming languages. The condition of a WHEN clause must return a Boolean result.

When a WHEN condition is met, the result from its corresponding THEN clause will return in the result column for that row. If no conditions are met, the ELSE clause may be used to specify a default result value. If there are no results found for a case expression, NULL is returned.

booktown=# 
SELECT isbn,

booktown-# 
       CASE WHEN cost > 20 THEN 'over $20.00 cost'

booktown-# 
            WHEN cost = 20 THEN '$20.00 cost'

booktown-# 
            ELSE 'under $20.00 cost'

booktown-# 
       END AS cost_range

booktown-# 
       FROM stock

booktown-# 
       LIMIT 8;

    isbn    |    cost_range
------------+-------------------
 0385121679 | over $20.00 cost
 039480001X | over $20.00 cost
 044100590X | over $20.00 cost
 0451198492 | over $20.00 cost
 0394900014 | over $20.00 cost
 0441172717 | under $20.00 cost
 0451160916 | over $20.00 cost
 0679803335 | $20.00 cost
(8 rows)

Adding to the power of case expressions are PostgreSQL's sub-queries, described in the Section called Using Sub-Queries ." As demonstrated in Example 4-51, a sub-query may be provided as a result within a conditional expression.

booktown=# 
SELECT isbn,

booktown-# 
       CASE WHEN cost > 20 THEN 'N/A - (Out of price range)'

booktown-# 
            ELSE (SELECT title FROM books b JOIN editions e

booktown(# 
                                    ON (b.id = e.book_id)

booktown(# 
                               WHERE e.isbn = stock.isbn)

booktown-# 
       END AS cost_range

booktown-# 
       FROM stock

booktown-# 
       ORDER BY cost_range ASC

booktown-# 
       LIMIT 8;

    isbn    |         cost_range
------------+-----------------------------
 0451457994 | 2001: A Space Odyssey
 0394800753 | Bartholomew and the Oobleck
 0441172717 | Dune
 0760720002 | Little Women
 0385121679 | N/A - (Out of price range)
 039480001X | N/A - (Out of price range)
 044100590X | N/A - (Out of price range)
 0451198492 | N/A - (Out of price range)
(8 rows)

In Example 4-51, any book found to have a cost of less than 20 has its title returned via a sub-select to the books table, along with its ISBN from the main query to the stock table.