CASE expressions

The CASE expression is a generic conditional expression that can be used wherever an expression is valid.

The CASE expression is a generic conditional expression that can be used wherever an expression is valid. It is 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 result is false, 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 examples show two uses of the CASE statement.

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

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