7.3. Conditional Expressions

CASE

The standard SQL CASE expression has two forms.The “simple” form searches each value expression from left to rightuntil it finds one that equals expression:

  1. CASE expression
  2. WHEN value THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

The result for the matching value is returned.If no match is found, the result from the ELSE clause isreturned if it exists, otherwise null is returned. Example:

  1. SELECT a,
  2. CASE a
  3. WHEN 1 THEN 'one'
  4. WHEN 2 THEN 'two'
  5. ELSE 'many'
  6. END

The “searched” form evaluates each boolean condition from leftto right until one is true and returns the matching result:

  1. CASE
  2. WHEN condition THEN result
  3. [ WHEN ... ]
  4. [ ELSE result ]
  5. END

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

  1. SELECT a, b,
  2. CASE
  3. WHEN a = 1 THEN 'aaa'
  4. WHEN b = 2 THEN 'bbb'
  5. ELSE 'ccc'
  6. END

IF

The IF function is actually a language constructthat is equivalent to the following CASE expression:

  1. CASE
  2. WHEN condition THEN true_value
  3. [ ELSE false_value ]
  4. END
  • 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

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

NULLIF

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

TRY

  • try(expression)
  • Evaluate an expression and handle certain types of errors by returningNULL.
In cases where it is preferable that queries produce NULL or default valuesinstead of failing when corrupt or invalid data is encountered, the TRYfunction may be useful. To specify default values, the TRY function can beused 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

Examples

Source table with some invalid data:

  1. SELECT * FROM shipping;
  1. origin_state | origin_zip | packages | total_cost
  2. --------------+------------+----------+------------
  3. California | 94131 | 25 | 100
  4. California | P332a | 5 | 72
  5. California | 94025 | 0 | 155
  6. New Jersey | 08544 | 225 | 490
  7. (4 rows)

Query failure without TRY:

  1. SELECT CAST(origin_zip AS BIGINT) FROM shipping;
  1. Query failed: Can not cast 'P332a' to BIGINT

NULL values with TRY:

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

  1. origin_zip

  1. 94131

NULL 94025 08544 (4 rows)

Query failure without TRY:

  1. SELECT total_cost / packages AS per_package FROM shipping;
  1. Query failed: / by zero

Default values with TRY and COALESCE:

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

  1. per_package

  1. 4
  2. 14
  3. 0
  4. 19

(4 rows)