CASE expressions

CASE expressions are generic conditional expressions that can be used wherever an expression is valid.

CASE expressions are generic conditional expressions that can be used wherever an expression is valid. They are similar to CASE and IF/THEN/ELSE statements in other languages.

Syntax (form 1)

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

Parameters

`condition`	An expression that returns a Boolean (true/false) result. If the condition evaluates to `FALSE` or `NULL`, subsequent `WHEN` clauses are evaluated in the same way.
`result`	Specifies the value to return when the associated `condition` is true.
`ELSE` `result`	If no `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.

Syntax (form 2)

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

Parameters

`expression`	An expression that is evaluated and compared to all the `value` specifications in `WHEN` clauses until one is found that is equal.
`value`	Specifies a value to compare to the `expression`.
`result`	Specifies the value to return when the `expression` is equal to the specified `value`.
`ELSE` `result`	Specifies the value to return when the `expression` is not equal to any `value`; if no `ELSE` clause is specified, the value returned is null.

Notes

The data types of all result expressions must be convertible to a single output type.

Examples

The following example demonstrates usage of CASE statements:

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

Special example

A CASE expression does not evaluate subexpressions that are not needed to determine the result. You can use this behavior to avoid division-by-zero errors, as shown by the following example:

=> SELECT x FROM T1 WHERE
      CASE WHEN x <> 0 THEN y/x > 1.5
      ELSE false
    END;