This section describes the SQL-compliant conditional expressions available in PostgreSQL.
If your needs go beyond the capabilities of these conditional expressions, you might want to consider writing a server-side function in a more expressive programming language.
Although COALESCE
, GREATEST
, and
LEAST
are syntactically similar to functions, they are
not ordinary functions, and thus cannot be used with explicit
VARIADIC
array arguments.
CASE
The SQL CASE
expression is a
generic conditional expression, similar to if/else statements in
other programming languages:
CASE WHENcondition
THENresult
[WHEN ...] [ELSEresult
] END
CASE
clauses can be used wherever
an expression is valid. Each condition
is an
expression that returns a boolean
result. If the condition's
result is true, the value of the CASE
expression is the
result
that follows the condition, and the
remainder of the CASE
expression is not processed. If the
condition's result is not true, any subsequent WHEN
clauses
are examined in the same manner. If no WHEN
condition
yields true, the value of the
CASE
expression is the result
of the
ELSE
clause. If the ELSE
clause is
omitted and no condition is true, the result is null.
An example:
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.
See 10.5절 for more details.
There is a “simple” form of CASE
expression
that is a variant of the general form above:
CASEexpression
WHENvalue
THENresult
[WHEN ...] [ELSEresult
] END
The first
expression
is computed, then compared to
each of the value
expressions in the
WHEN
clauses until one is found that is equal to it. If
no match is found, the result
of the
ELSE
clause (or a null value) is returned. This is similar
to the switch
statement in C.
The example above can be written using the simple
CASE
syntax:
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
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:
SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5 ELSE false END;
As described in 4.2.14절, there are various
situations in which subexpressions of an expression are evaluated at
different times, so that the principle that “CASE
evaluates only necessary subexpressions” is not ironclad. For
example a constant 1/0
subexpression will usually result in
a division-by-zero failure at planning time, even if it's within
a CASE
arm that would never be entered at run time.
COALESCE
COALESCE
(value
[, ...])
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, for example:
SELECT COALESCE(description, short_description, '(none)') ...
This returns description
if it is not null, otherwise
short_description
if it is not null, otherwise (none)
.
The arguments must all be convertible to a common data type, which will be the type of the result (see 10.5절 for details).
Like a CASE
expression, COALESCE
only
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 NVL
and IFNULL
, which are used in some other
database systems.
NULLIF
NULLIF
(value1
,value2
)
The NULLIF
function returns a null value if
value1
equals value2
;
otherwise it returns value1
.
This can be used to perform the inverse operation of the
COALESCE
example given above:
SELECT NULLIF(value, '(none)') ...
In this example, if value
is (none)
,
null is returned, otherwise the value of value
is returned.
The two arguments must be of comparable types.
To be specific, they are compared exactly as if you had
written
, so there must be a
suitable value1
= value2
=
operator available.
The result has the same type as the first argument — but there is
a subtlety. What is actually returned is the first argument of the
implied =
operator, and in some cases that will have
been promoted to match the second argument's type. For
example, NULLIF(1, 2.2)
yields numeric
,
because there is no integer
=
numeric
operator,
only numeric
=
numeric
.
GREATEST
and LEAST
GREATEST
(value
[, ...])
LEAST
(value
[, ...])
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
(see 10.5절 for details). NULL values
in the list are ignored. The result will be NULL only if all the
expressions evaluate to NULL.
Note that GREATEST
and LEAST
are 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.