CASE Expressions
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 |
Is an expression that returns a boolean (true/false) result. If the result is false, subsequent WHEN clauses are evaluated in the same manner. |
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 |
Specifies a value to compare to the |
result |
Specifies the value to return when the |
ELSE result |
Specifies the value to return when the |
Notes
The data types of all the 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;