Conditional Expressions
Several SQL-compliant conditional expressions are available in Extreme DPS, namely CASE WHEN
, COALESCE
, NULLIF
, and GREATEST
and LEAST
.
- Extreme DPS does not support
interval
in conditional expressions. COALESCE
,GREATEST
, andLEAST
cannot be used with explicitVARIADIC
array arguments, though they have similar syntaxes like functions.
CASE WHEN
The CASE WHEN
expression is a generic conditional expression, similar to if/else statements in other programing languages.
Syntax
CASE WHEN <condition> THEN <result>
[WHEN ...]
[ELSE <result>]
END
The CASE
clauses can be used whenever an expression is valid. If the condition is true, result <result1>
will be returned, and the remainder of the CASE
expression will not be processed.
Arguments
-
<condition>
The condtion that evaluate to a Boolean value (
true
,false
, orNULL
). -
<result>
The result returned if the
<condition>
it follows is true.
Examples
SELECT * FROM data;
id
---
123
456
789
SELECT id,
CASE WHEN id=123 THEN 'john'
WHEN id=456 THEN 'tom'
ELSE 'others'
END
FROM data;
id | case
-----+-------
123 | john
456 | tom
789 | others
Usage notes
The data types of all the <result>
expressions must be convertible to a single output type. For more information, see UNION, CASE, and Related Constructs in the PostgreSQL documentation.
COALESCE
Returns the first non-null expression, or NULL
if all arguments are NULL
. The return type of COALESCE
is not fixed and is determined by the data type of the returned expression.
Syntax
COALESCE(<value> [, ...])
Arguments
<value>
: the argument to verify whether it is null.
Examples
SELECT * FROM data;
name
---
john
tom
NULL
SELECT COALESCE(null, name) as r_
FROM data;
r_
-----
john
tom
null
Usage notes
You can use COALESCE
to substitute a default value for null values when data is retrieved for display. The following provides an example syntax:
SELECT COALESCE(<description>, <short_description>, '(none)') ...
In this syntax:
<description>
will be returned if it is not null.<short_description>
will be returned if<description>
is null and<short_description>
is not null.NULL
will be returned in other cases.
NULLIF
The NULLIF
function returns NULL
when the first expression is equal to the second expression. Otherwise, it returns the first expression.
Syntax
NULLIF(value1, value2)
Arguments
-
<value1>
The first expression to compare.
-
<value2>
The second expression that evaluates to the same data type of
<value1>
.
Usage notes
The two expressions must be of comparable types. This implies that an equation <value1>=<value2>
. A =
operator is available. Therefore, in most cases, the return type is of the same data type as <value1>
. In some special cases, the return type is the same as <value2>
. For example, NULLIF(1, 2.2)
yields numeric
. This is because the =
operator cannot be used to compare integer
and numeric
and can only be used to compare numeric
and numeric
.
You can use this function to perform the inverse operations of COALESCE
. The following provides an example syntax:
SELECT NULLIF(<value>, '(none)') ...
In this syntax, if <value>
is (none)
, null will be returned. Otherwise, the value of <value>
will be returned.
GREATEST or LEAST
The GREATEST
or LEAST
function returns the largest or smallest value from a list of expressions.
Syntax
GREATEST(<value> [, ...])
LEAST(<value> [, ...])
Arguments
<value>
: the expression to compare.
Examples
For GREATEST
:
SELECT * FROM data;
col_1 | col_2
------+-------
110 | 100
99 | 130
199 | 300
NULL | 999
SELECT GREATEST(col_1, col_2, 128) as r_
FROM data;
r_
-----
128
130
300
NULL
For LEAST
:
SELECT * FROM data;
col_1 | col_2
------+-------
110 | 100
99 | 130
199 | 300
NULL | 999
SELECT LEAST(col_1, col_2, 128) as r_
FROM data;
r_
-----
100
99
128
NULL
Usage notes
All values in the list must be convertible to a common data types. For more information, see UNION, CASE, and Related Constructs in the PostgreSQL documentation.
NULL
values in the list will be ignored.
If all values in the list of expressions are NULL
, NULL
will be returned.