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
|
An expression that returns a Boolean (true/false) result. If the result is false, subsequent |
result
|
Specifies the value to return when the associated condition is true. |
ELSE result
|
If no condition is true then the value of the |
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 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;