Categories:
Conditional Expression Functions

IFF

Single-level if-then-else expression. Similar to CASE, but only allows a single condition.

If condition evaluates to TRUE, returns expr1, otherwise returns expr2.

Syntax

IFF( <condition> , <expr1> , <expr2> )

Arguments

condition
The condition is an expression that should evaluate to a BOOLEAN value (True, False, or NULL).
expr1
A general expression. This value is returned if the condition is true.
expr2
A general expression. This value is returned if the condition is not true (i.e. if it is false or NULL).

Usage Notes

  • The condition can include a SELECT statement containing set operators, such as UNION, INTERSECT, EXCEPT, and MINUS. When using set operators, make sure that data types are compatible. For details, see the General Usage Notes in the Set Operators topic.

Collation Details

The value returned from the function retains the collation specification of the highest-precedence collation of the THEN/ELSE arguments.

Examples

The following examples demonstrate the IFF function:

select iff(True, 'true', 'false');
+----------------------------+
| IFF(TRUE, 'TRUE', 'FALSE') |
|----------------------------|
| true                       |
+----------------------------+
select iff(False, 'true', 'false');
+-----------------------------+
| IFF(FALSE, 'TRUE', 'FALSE') |
|-----------------------------|
| false                       |
+-----------------------------+
select iff(NULL, 'true', 'false');
+----------------------------+
| IFF(NULL, 'TRUE', 'FALSE') |
|----------------------------|
| false                      |
+----------------------------+
SELECT val, IFF(val::int = val, 'integer', 'non-integer')
    FROM ( SELECT column1 as val
               FROM values(1.0), (1.1), (-3.1415), (-5.000), (null) )
    ORDER BY val DESC;
+---------+-----------------------------------------------+
|     VAL | IFF(VAL::INT = VAL, 'INTEGER', 'NON-INTEGER') |
|---------+-----------------------------------------------|
|    NULL | non-integer                                   |
|  1.1000 | non-integer                                   |
|  1.0000 | integer                                       |
| -3.1415 | non-integer                                   |
| -5.0000 | integer                                       |
+---------+-----------------------------------------------+