- Categories:
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 aSELECT
statement containing set operators, such asUNION
,INTERSECT
,EXCEPT
, andMINUS
. 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 | +---------+-----------------------------------------------+