Conditional expressions

CASE

The standard SQL CASE expression has two forms. The "simple" form searches each value expression from left to right until it finds one that equals expression:

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

The result is the value corresponding to the matched condition. In case no match is identified, the result from the ELSE clause, if present, is returned; otherwise, null is the result. For example:

SELECT a,
       CASE a
           WHEN 1 THEN 'one'
           WHEN 2 THEN 'two'
           ELSE 'many'
       END

The "searched" form assesses each boolean condition from left to right until one becomes true and then returns the corresponding result:

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

If no conditions are true, the result from the ELSE clause is returned if it exists, otherwise null is returned.For Example:

SELECT a, b,
       CASE
           WHEN a = 1 THEN 'aaa'
           WHEN b = 2 THEN 'bbb'
           ELSE 'ccc'
       END

SQL routines can use CASE statementsthat use a slightly different syntax from the CASE expressions. Specifically note the requirements for terminating each clause with a semicolon ; and the usage of END CASE.

IF()

The IF expression comes in two forms: one provides only a true_value, while the other offers both a true_value and a false_value.

Function	Description
if(condition, true_value)	Evaluates and returns true_value if condition is true, otherwise null is returned and true_value is not evaluated.
if(condition, true_value, false_value)	Evaluates and returns true_value if condition is true, otherwise evaluates and returns false_value.

COALESCE

Function	Description
coalesce(value1, value2[, ...])	Returns the first non-null value in the argument list. Like a CASE expression, arguments are only evaluated if necessary.

NULLIF

Function	Description
nullif(value1, value2)	Returns null if value1 equals value2, otherwise returns value1.

TRY()

In cases where it is preferable for queries to produce NULL or default values instead of failing when corrupt or invalid data is encountered, the TRY function may be useful. To specify default values, the TRY function can be used in conjunction with the COALESCE function.

The following errors are handled by TRY:

- Division by zero
- Invalid cast or function argument
- Numeric value out of range

Function	Description
try(expression)	Evaluates an expression and handles certain types of errors by returning NULL.

Examples

Source table with some invalid data:

SELECT * FROM shipping;

 origin_state | origin_zip | packages | total_cost
--------------+------------+----------+------------
 California   |      94131 |       25 |        100
 California   |      P332a |        5 |         72
 California   |      94025 |        0 |        155
 New Jersey   |      08544 |      225 |        490
(4 rows)

Query failure without TRY:

SELECT CAST(origin_zip AS BIGINT) FROM shipping;

Query failed: Cannot cast 'P332a' to BIGINT

NULL values with TRY:

SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;

 origin_zip
------------
      94131
       NULL
      94025
      08544
(4 rows)

Query failure without TRY:

SELECT total_cost / packages AS per_package FROM shipping;

Query failed: Division by zero

Default values with TRY and COALESCE:

SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;

 per_package
-------------
          4
         14
          0
         19
(4 rows)