Conditional Expressions v11
The following section describes the SQL-compliant conditional expressions available in Advanced Server.
CASE
The SQL CASE
expression is a generic conditional expression, similar to if/else statements in other languages:
CASE
clauses can be used wherever an expression is valid. condition
is an expression that returns a BOOLEAN
result. If the result is TRUE
then the value of the CASE
expression is the result
that follows the condition. If the result is FALSE
any subsequent WHEN
clauses are searched in the same manner. If no WHEN 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
.
An example:
The data types of all the result
expressions must be convertible to a single output type.
The following “simple” CASE
expression is a specialized variant of the general form above:
The expression
is computed and compared to all the value
specifications in the WHEN
clauses until one is found that is equal. If no match is found, the result
in the ELSE
clause (or a null value) is returned.
The example above can be written using the simple CASE
syntax:
A CASE
expression 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:
COALESCE
The COALESCE
function 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 or further computation. For example:
Like a CASE
expression, COALESCE
will not evaluate arguments that are not 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 NVL
and IFNULL
, which are used in some other database systems.
NULLIF
The NULLIF
function returns a null value if value1
and value2
are equal; otherwise it returns value1
.
This can be used to perform the inverse operation of the COALESCE
example given above:
If value1
is (none), return a null, otherwise return value1
.
NVL
The NVL
function returns the first of its arguments that is not null. NVL
evaluates the first expression; if that expression evaluates to NULL
, NVL
returns the second expression.
The return type is the same as the argument types; all arguments must have the same data type (or be coercible to a common type). NVL
returns NULL
if all arguments are NULL
.''
is considered as unknown and if the arguments data type are not coercible to the common data type then NVL
throws an error.
Examples
The following example computes a bonus for non-commissioned employees, If an employee is a commissioned employee,
this expression returns the employees commission; if the employee is not a commissioned employee (that is, his commission isNULL
), this expression returns a bonus that is 10% of his salary.In the following example, the type of 1 is numeric and the type of '' is considered as unknown. Therefore PostgreSQL decides that the common type ought to be numeric and tries to interpret the empty string as a numeric value, which produces the indicated error.
NVL2
NVL2
evaluates an expression, and returns either the second or third expression, depending on the value of the first expression. If the first expression is not NULL
, NVL2
returns the value in expr2
; if the first expression is NULL
, NVL2
returns the value in expr3
.
The return type is the same as the argument types; all arguments must have the same data type (or be coercible to a common type).
The following example computes a bonus for commissioned employees - if a given employee is a commissioned employee, this expression returns an amount equal to 110% of his commission; if the employee is not a commissioned employee (that is, his commission is NULL
), this expression returns 0
.
GREATEST and LEAST
The GREATEST
and LEAST
functions 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. Null values in the list are ignored. The result will be null only if all the expressions evaluate to null.
Note
The GREATEST
and LEAST
are not in the SQL standard, but are a common extension.