Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions
Privacy Policy

  




 

 

Databases - Practical PostgreSQL
Previous Page Home Next Page

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.

Example 4-50. Using case expressions in statements

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.

Example 4-51. Using case expressions with sub-queries

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.

Databases - Practical PostgreSQL
Previous Page Home Next Page

 
 
  Published under the terms of the Open Publication License Design by Interspire