This section describes theSQL-compliant conditional expressions available inPostgreSQL.
If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a stored procedure in a more expressive programming language.
CASEexpression is a generic conditional expression, similar to if/else statements in other programming languages:
CASE WHEN condition THEN result [ WHEN ... ] [ ELSE result ] END
CASEclauses can be used wherever an expression is valid. Each
condition_is an expression that returns a
booleanresult. If the condition's result is true, the value of the
CASEexpression is the
resultthat follows the condition, and the remainder of the
CASEexpression is not processed. If the condition's result is not true, any subsequent
WHENclauses are examined in the same manner. If no
conditionyields true, the value of the
CASEexpression is the
ELSEclause. If the
ELSEclause is omitted and no condition is true, the result is null.
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
The data types of all the_
result_expressions must be convertible to a single output type. SeeSection 10.5for more details.
There is a“simple”form of
CASEexpression that is a variant of the general form above:
CASE expression WHEN value THEN result [ WHEN ... ] [ ELSE result ] END
expression_is computed, then compared to each of the
valueexpressions in the
WHENclauses until one is found that is equal to it. If no match is found, the
ELSEclause (or a null value) is returned. This is similar to the
switchstatement in C.
The example above can be written using the simple
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
CASEexpression does not evaluate any subexpressions that are not needed to determine the result. For example, this is a possible way of avoiding a division-by-zero failure:
SELECT ... WHERE CASE WHEN x < > 0 THEN y/x > 1.5 ELSE false END;
As described inSection 4.2.14, there are various situations in which subexpressions of an expression are evaluated at different times, so that the principle that“
CASEevaluates only necessary subexpressions”is not ironclad. For example a constant
1/0subexpression will usually result in a division-by-zero failure at planning time, even if it's within a
CASEarm that would never be entered at run time.
COALESCE ( value [ , ... ])
COALESCEfunction returns the first of its arguments that is not null. Null is returned only if all arguments are null. It is often used to substitute a default value for null values when data is retrieved for display, for example:
SELECT COALESCE(description, short_description, '(none)') ...
descriptionif it is not null, otherwise
short_descriptionif it is not null, otherwise
COALESCEonly evaluates the arguments that are needed to determine the result; that is, arguments to the right of the first non-null argument are not evaluated. This SQL-standard function provides capabilities similar to
IFNULL, which are used in some other database systems.
NULLIF ( value1 , value2 )
NULLIFfunction returns a null value if
value2; otherwise it returns
value1_. This can be used to perform the inverse operation of the
COALESCEexample given above:
SELECT NULLIF(value, '(none)') ...
In this example, if
(none), null is returned, otherwise the value of
GREATEST ( value [ , ... ])
LEAST ( value [ , ... ])
LEASTfunctions select the largest or smallest value from a list of any number of expressions. The expressions must all be convertible to a common data type, which will be the type of the result (seeSection 10.5for details). NULL values in the list are ignored. The result will be NULL only if all the expressions evaluate to NULL.
LEASTare not in the SQL standard, but are a common extension. Some other databases make them return NULL if any argument is NULL, rather than only when all are NULL.