Conditional Expressions

6.12. Conditional Expressions

This section describes the SQL-compliant conditional expressions available in PostgreSQL.

Tip: If your needs go beyond the capabilities of these conditional expressions you might want to consider writing a stored procedure in a more expressive programming language.

6.12.1. CASE

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages. CASE clauses can be used wherever an expression is valid. condition is an expression that returns a boolean result. If the result is true then the value of the CASE expression is result. If the result is false any subsequent WHEN clauses are searched in the same manner. If no WHEN condition is true then the value of the case expression is the result in the ELSE clause. If the ELSE clause is omitted and no condition matches, the result is null.

An example:

=> SELECT * FROM test;
 a
---
 1
 2
 3

=> SELECT a,
          CASE WHEN a=1 THEN 'one'
               WHEN a=2 THEN 'two'
               ELSE 'other'
          END
    FROM test;
 a | case
---+-------
 1 | one
 2 | two
 3 | other

The data types of all the result expressions must be coercible to a single output type. See Section 7.5 for more detail.

CASE expression
    WHEN value THEN result
    [WHEN ...]
    [ELSE result]
END

This "simple" CASE expression is a specialized variant of the general form above. The expression is computed and compared to all the values in the WHEN clauses until one is found that is equal. If no match is found, the result in the ELSE clause (or a null value) is returned. This is similar to the switch statement in C.

The example above can be written using the simple CASE syntax:

=> SELECT a,
          CASE a WHEN 1 THEN 'one'
                 WHEN 2 THEN 'two'
                 ELSE 'other'
          END
    FROM test;
 a | case
---+-------
 1 | one
 2 | two
 3 | other

6.12.2. COALESCE

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null. This is often useful to substitute a default value for null values when data is retrieved for display, for example:

SELECT COALESCE(description, short_description, '(none)') ...

6.12.3. NULLIF

NULLIF(value1, value2)

The NULLIF function returns a null value if and only if value1 and value2 are equal. Otherwise it returns value1. This can be used to perform the inverse operation of the COALESCE example given above:

SELECT NULLIF(value, '(none)') ...

Tip: COALESCE and NULLIF are just shorthand for CASE expressions. They are actually converted into CASE expressions at a very early stage of processing, and subsequent processing thinks it is dealing with CASE. Thus an incorrect COALESCE or NULLIF usage may draw an error message that refers to CASE.

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
You're at the end of the road again.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!
Paid advertisements: Free Electronics