Conditional expressions v16

SQL-compliant conditional expressions are available in EDB Postgres Advanced Server.

CASE

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other languages:

CASE WHEN condition THEN result
   [ WHEN ... ]
   [ ELSE result ]
END

You can use CASE clauses 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.

For example:

SELECT * FROM test;

 a
---
 1
 2
 3
(3 rows)

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
(3 rows)

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:

CASE expression
    WHEN value THEN result
  [ WHEN ... ]
  [ ELSE result ]
END

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.

This same example 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
(3 rows)

A CASE expression doesn't evaluate any subexpressions that aren't 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;

COALESCE

The COALESCE function returns the first of its arguments that isn't null. Null is returned only if all arguments are null.

COALESCE(value [, value2 ] ... )

It's often used to substitute a default value for null values when data is retrieved for display or further computation. For example:

SELECT COALESCE(description, short_description, '(none)') ...

Like a CASE expression, COALESCE doesn't evaluate arguments that aren't needed to determine the result. Arguments to the right of the first non-null argument aren't 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.

NULLIF(value1, value2)

You can use this to perform the inverse operation of the COALESCE example:

SELECT NULLIF(value1, '(none)') ...

If value1 is (none), return a null. Otherwise return value1.

NVL

The NVL function returns the first of its arguments that isn't null. NVL evaluates the first expression. If that expression evaluates to NULL, NVL returns the second expression.

NVL(expr1, expr2)

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 aren't coercible to the common data type, then NVL throws an error.

Examples

This example computes a bonus for noncommissioned employees. If an employee is a commissioned employee, this expression returns the employee's commission. If the employee isn't a commissioned employee, that is, their commission is NULL, this expression returns a bonus that's 10% of their salary.

bonus = NVL(emp.commission, emp.salary * .10)

In this example, the type of 1 is numeric and the type of '' is considered as unknown. Therefore PostgreSQL decides that the common type is numeric. It tries to interpret the empty string as a numeric value, which produces the indicated error:

edb=# select nvl('',1);
ERROR:  invalid input syntax for type numeric: ""

In this example, if 33 is type casted to double precision, it converts to double precision and returns the value as double precision. If 33 is type casted to numeric, it converts to numeric and returns the value as numeric.

edb=# select NVL(33::double precision,0), pg_typeof(NVL(33::double precision,0)), pg_typeof(NVL(33::numeric,0));

 nvl |    pg_typeof     | pg_typeof
-----+------------------+-----------
  33 | double precision | numeric
(1 row)

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 isn't NULL, NVL2 returns the value in expr2. If the first expression is NULL, NVL2 returns the value in expr3.

NVL2(expr1, expr2, 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.

This example computes a bonus for commissioned employees. If a given employee is a commissioned employee, this expression returns an amount equal to 110% of their commission. If the employee isn't a commissioned employee, that is, their commission is NULL, this expression returns 0.

bonus = NVL2(emp.commission, emp.commission * 1.1, 0)

NANVL

The NANVL function returns the first of its arguments that is not-a-number (NaN) value. NANVL evaluates the first expression. If that expression evaluates to a NAN value, NANVL returns the second expression. If the first expression evaluates to a number value, NANVL returns the first expression. The NANVL function is useful only for floating-point numbers of type BINARY_FLOAT or BINARY_DOUBLE.

NANVL(expr1,expr2)

This function takes any numeric data type or any non-numeric data type that can be implicitly converted to a numeric data type as arguments. EDB Postres Advanced Server determines the argument with the highest numeric precedence, implicitly converts the remaining arguments to that data type, and returns that data type.

Examples

SELECT nanvl(124346, 1), nanvl('NaN', 2), nanvl(124346::int, 3), nanvl(124346::int8, 4);

 nanvl  | nanvl | nanvl  | nanvl  
--------+-------+--------+--------
 124346 |     2 | 124346 | 124346
(1 row)

SELECT nanvl('NaN', 1::int2), nanvl(124346::int, 2::int2), nanvl(124346::int8, 3::int2);

 nanvl | nanvl  | nanvl  
-------+--------+--------
     1 | 124346 | 124346
(1 row)

select nanvl('NAN',2::int2);

 nanvl 
-------
     2
(1 row)

LNNVL

The LNNVL function provides a concise way to evaluate a condition when one or both operands of the condition might be null.

LNNVL(condition)

The function returns TRUE if the condition is FALSE or UNKNOWN and FALSE if the condition is TRUE.

The condition can evaluate any scalar values but can't be a compound condition containing AND, OR, or BETWEEN.

Use the LNNVL function:

• In the WHERE clause of a query.
• In the WHEN condition in a searched CASE expression.
• Anywhere a scalar expression appears, even in contexts where the IS [NOT] NULL, AND, or OR conditions aren't valid but otherwise are required to account for potential nulls.

Examples

Create a table and insert data:

CREATE TABLE t1 (id INT, col1 INT);
INSERT INTO t1 VALUES (10, NULL), (1,1),(2,2), (3,3);

Use the LNNVL function in a SELECT query:

SELECT * FROM t1 WHERE lnnvl(col1 > 2) ORDER BY id;

id | col1 
----+------+  
 1 |    1
 2 |    2 
10 |     
(3 rows)

SELECT * FROM t1 WHERE lnnvl(col1 < 2) ORDER BY id;

id | col1 
----+------+  
 2 |    2
 3 |    3
10 |     
(3 rows)

SELECT * FROM t1 WHERE lnnvl(col1 > NULL) ORDER BY id;

id | col1 
----+------+  
 1 |    1  
 2 |    2
 3 |    3
10 |     
(4 rows)

GREATEST and LEAST

The GREATEST and LEAST functions select the largest or smallest value from a list of any number of expressions.

GREATEST(value [, value2 ] ... )
LEAST(value [, value2 ] ... )

All of the expressions must be convertible to a common data type, which becomes the type of the result. Null values in the list are ignored. The result is null only if all the expressions evaluate to null.

• On this page
• CASE
• COALESCE
• NULLIF
• NVL
• NVL2
• NANVL
• LNNVL
• GREATEST and LEAST