Skip to main content

Conditional Expressions

Several SQL-compliant conditional expressions are available in Extreme DPS, namely CASE WHEN, COALESCE, NULLIF, and GREATEST and LEAST.

info
  • Extreme DPS does not support interval in conditional expressions.
  • COALESCE, GREATEST, and LEAST cannot be used with explicit VARIADIC 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, or NULL).

  • <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.