======================= 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:

.. code-block:: none

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

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

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

The "searched" form evaluates each boolean condition from left to right until one is true and returns the matching result:

.. code-block:: none

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. Example::

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

IF

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

.. code-block:: none

CASE
    WHEN condition THEN true_value
    [ ELSE false_value ]
END

.. function:: if(condition, true_value)

Evaluates and returns ``true_value`` if ``condition`` is true,
otherwise null is returned and ``true_value`` is not evaluated.

.. function:: if(condition, true_value, false_value)

Evaluates and returns ``true_value`` if ``condition`` is true,
otherwise evaluates and returns ``false_value``.

COALESCE

.. function:: 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:: nullif(value1, value2)

Returns null if ``value1`` equals ``value2``, otherwise returns ``value1``.

TRY

.. function:: try(expression)

Evaluate an expression and handle certain types of errors by returning
``NULL``.

In cases where it is preferable that queries 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 argument or invalid function argument
Numeric value out of range

Examples


Source table with some invalid data:

.. code-block:: sql

    SELECT * FROM shipping;

.. code-block:: none

     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``:

.. code-block:: sql

    SELECT CAST(origin_zip AS BIGINT) FROM shipping;

.. code-block:: none

    Query failed: Can not cast 'P332a' to BIGINT

``NULL`` values with ``TRY``:

.. code-block:: sql

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

.. code-block:: none

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

Query failure without ``TRY``:

.. code-block:: sql

    SELECT total_cost / packages AS per_package FROM shipping;

.. code-block:: none

    Query failed: / by zero

Default values with ``TRY`` and ``COALESCE``:

.. code-block:: sql

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

.. code-block:: none

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