Comparison Functions & Operators#

1. Comparison Operators#

Operator

Description

datatype < datatype → boolean

Less than

datatype > datatype → boolean

Greater than

datatype <= datatype → boolean

Less than or equal to

datatype >= datatype → boolean

Greater than or equal to

datatype = datatype → boolean

Equal

datatype <> datatype → boolean

Not equal

datatype != datatype → boolean

Not equal

NOTE: <> is the SQL notation for not equal. != is an alias, converted to <> at a very early stage of parsing.

Comparison operators are available for all built-in data types that have a natural ordering (numeric, string, date/time, …).

Arrays, composite types and ranges can be compared if their component data types are comparable.

It’s possible to compare values of related data types e.g. interger < bigint, by either:

  • cross-type comparison operators, if available

  • coercing the less general type to the more general during parsing

All comparison operators are binary operators that return boolean values, so expressions like 1 < 2 < 3 are not valid. Use BETWEEN to perform range tests.

2. Comparison Predicates#

Predicate

Description

Example(s)

datatype BETWEEN datatype AND datatype → boolean

Between (inclusive of the range endpoints).

2 BETWEEN 1 AND 3 → t
2 BETWEEN 3 AND 1 → f

datatype NOT BETWEEN datatype AND datatype → boolean

Not between (the negation of BETWEEN).

2 NOT BETWEEN 1 AND 3 → f

datatype BETWEEN SYMMETRIC datatype AND datatype → boolean

Between, after sorting the two endpoint values.

2 BETWEEN SYMMETRIC 3 AND 1 → t

datatype NOT BETWEEN SYMMETRIC datatype AND datatype → boolean

Not between, after sorting the two endpoint values.

2 NOT BETWEEN SYMMETRIC 3 AND 1 → f

datatype IS DISTINCT FROM datatype → boolean

Not equal, treating null as a comparable value.

1 IS DISTINCT FROM NULL → t
(rather than NULL) NULL IS DISTINCT FROM NULL → f (rather than NULL)

datatype IS NOT DISTINCT FROM datatype → boolean

Equal, treating null as a comparable value.

1 IS NOT DISTINCT FROM NULL → f
(rather than NULL) NULL IS NOT DISTINCT FROM NULL → t (rather than NULL)

datatype IS NULL → boolean

Test whether value is null.

1.5 IS NULL → f

datatype IS NOT NULL → boolean

Test whether value is not null.

‘null’ IS NOT NULL → t

datatype ISNULL → boolean

Test whether value is null (nonstandard syntax).

datatype NOTNULL → boolean

Test whether value is not null (nonstandard syntax).

boolean IS TRUE → boolean

Test whether boolean expression yields true.

true IS TRUE → t
NULL::boolean IS TRUE → f (rather than NULL)

boolean IS NOT TRUE → boolean

Test whether boolean expression yields false or unknown.

true IS NOT TRUE → f
NULL::boolean IS NOT TRUE → t (rather than NULL)

boolean IS FALSE → boolean

Test whether boolean expression yields false.

true IS FALSE → f
NULL::boolean IS FALSE → f (rather than NULL)

boolean IS NOT FALSE → boolean

Test whether boolean expression yields true or unknown.

true IS NOT FALSE → t
NULL::boolean IS NOT FALSE → t (rather than NULL)

boolean IS UNKNOWN → boolean

Test whether boolean expression yields unknown.

true IS UNKNOWN → f
NULL::boolean IS UNKNOWN → t (rather than NULL)

boolean IS NOT UNKNOWN → boolean

Test whether boolean expression yields true or false.

true IS NOT UNKNOWN → t
NULL::boolean IS NOT UNKNOWN → f (rather than NULL)`

BETWEEN simplifies range tests. Endpoint values are treated as included.

a BETWEEN x AND y
a >= x AND a <= y

BETWEEN SYMMETRIC automatically swaps endpoint values if that to the left of AND is >= that to the right, so that a non-empty range is always implied.

mydb=> SELECT 3 BETWEEN 7 AND 2;
 ?column? 
----------
 f
(1 row)

mydb=> SELECT 3 BETWEEN SYMMETRIC 7 AND 2;
 ?column? 
----------
 t
(1 row)

NOTE: The use of AND in BETWEEN syntax creates ambiguity with the use of AND as a logical operator, so only a limited set of expressions are allowed as the second argument of a BETWEEN clause.

To write complex sub-expressions in BETWEEN, use ().

Ordinary comparison operators yield null (“unknown”) when either input is null e.g. 7 = NULL and 7 <> NULL both yield null.

For non-null inputs, IS DISTINCT FROM is the same as <>. But if both inputs are null it returns false, and if only one input is null it returns true.

IS NOT DISTINCT FROM is similar to = for non-null inputs, but it returns true when both inputs are null, and false when only one input is null.

Use IS NULL and IS NOT NULL to check whether a value is null or not.

ISNULL and NOTNULL work too but are not standard.

If the expression is row-valued, IS NULL is true when the row expression itself is null or if all its fields are null; whereas IS NOT NULL is true when the row expression itself is non-null and all its fields are non-null.

Thus IS NULL and IS NOT NULL don’t always return inverse results for row-valued expressions. A row-valued expression with both null and non-null fields returns false for both tests.

row IS DISTINCT FROM NULL and row IS NOT DISTINCT FROM NULL simply check the overal row value, with no additional checks on row fields.

Boolean values can be tested using predicates:

boolean_expression IS TRUE
boolean_expression IS NOT TRUE
boolean_expression IS FALSE
boolean_expression IS NOT FALSE
boolean_expression IS UNKNOWN
boolean_expression IS NOT UNKNOWN

These return true or false, never null.

Null input is treated as the logical value “unknown”, so IS UNKNOWN and IS NOT UNKNOWN are effectively the same as IS NULL and IS NOT NULL when the input expression is boolean.

3. Comparison Functions#

Function

Description

Example(s)

num_nonnulls ( VARIADIC “any” ) → integer

Returns the number of non-null arguments.

num_nonnulls(1, NULL, 2) → 2

num_nulls ( VARIADIC “any” ) → integer

Returns the number of null arguments.

num_nulls(1, NULL, 2) → 1