Comparison operators and functions for PostgreSQL - PolarDB

PolarDB for PostgreSQL (Compatible with Oracle) supports the standard SQL comparison operators, predicates, and functions described on this page.

Comparison operators

All comparison operators return a boolean value. They are available for all built-in data types with a natural ordering, including numeric, string, and date/time types. Arrays, composite types, and ranges can also be compared if their component data types are comparable.

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

<> is the standard SQL notation for "not equal". != is an alias that is converted to <> at a very early stage of parsing, so it is not possible to implement != and <> operators that do different things.

Cross-type comparisons work in many cases — for example, integer > bigint is valid. The parser coerces the less-general type to the more-general type and applies that type's comparison operator.

Because all comparison operators are binary operators, expressions like 1 < 2 < 3 are not valid — there is no < operator that accepts a boolean left-hand operand. Use the BETWEEN predicate for range tests.

Comparison predicates

Comparison predicates behave like operators but have special syntax required by the SQL standard.

Predicate	Description	Example
`datatype BETWEEN datatype AND datatype → boolean`	Between, inclusive of range endpoints. Equivalent to `a >= x AND a <= y`.	`2 BETWEEN 1 AND 3` → `t` `2 BETWEEN 3 AND 1` → `f`
`datatype NOT BETWEEN datatype AND datatype → boolean`	Not between. 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. Returns `false` if both inputs are null; returns `true` if only one input is null.	`1 IS DISTINCT FROM NULL` → `t` `NULL IS DISTINCT FROM NULL` → `f`
`datatype IS NOT DISTINCT FROM datatype → boolean`	Equal, treating null as a comparable value. Returns `true` if both inputs are null; returns `false` if only one input is null.	`1 IS NOT DISTINCT FROM NULL` → `f` `NULL IS NOT DISTINCT FROM NULL` → `t`
`datatype IS NULL → boolean`	Test whether a value is null.	`1.5 IS NULL` → `f`
`datatype IS NOT NULL → boolean`	Test whether a value is not null.	`'null' IS NOT NULL` → `t`
`datatype ISNULL → boolean`	Test whether a value is null (nonstandard syntax).	—
`datatype NOTNULL → boolean`	Test whether a value is not null (nonstandard syntax).	—
`boolean IS TRUE → boolean`	Test whether a boolean expression yields true. Null input is treated as unknown, not true.	`true IS TRUE` → `t` `NULL::boolean IS TRUE` → `f`
`boolean IS NOT TRUE → boolean`	Test whether a boolean expression yields false or unknown.	`true IS NOT TRUE` → `f` `NULL::boolean IS NOT TRUE` → `t`
`boolean IS FALSE → boolean`	Test whether a boolean expression yields false.	`true IS FALSE` → `f` `NULL::boolean IS FALSE` → `f`
`boolean IS NOT FALSE → boolean`	Test whether a boolean expression yields true or unknown.	`true IS NOT FALSE` → `t` `NULL::boolean IS NOT FALSE` → `t`
`boolean IS UNKNOWN → boolean`	Test whether a boolean expression yields unknown. Effectively the same as `IS NULL`, but the input must be of boolean type.	`true IS UNKNOWN` → `f` `NULL::boolean IS UNKNOWN` → `t`
`boolean IS NOT UNKNOWN → boolean`	Test whether a boolean expression yields true or false. Effectively the same as `IS NOT NULL`, but the input must be of boolean type.	`true IS NOT UNKNOWN` → `t` `NULL::boolean IS NOT UNKNOWN` → `f`

BETWEEN

BETWEEN is inclusive of its endpoints:

a BETWEEN x AND y
-- is equivalent to:
a >= x AND a <= y

BETWEEN SYMMETRIC automatically swaps the two endpoints if the left argument is greater than the right, so a non-empty range is always implied.

The AND keyword inside a BETWEEN clause creates an ambiguity with AND as a logical operator. Only a limited set of expression types are allowed as the second argument of BETWEEN. Wrap complex sub-expressions in parentheses.

NULL handling

Ordinary comparison operators return null (meaning "unknown") when either input is null. For example, 7 = NULL and 7 <> NULL both yield null.

Use IS DISTINCT FROM and IS NOT DISTINCT FROM when you need null-safe comparisons:

a IS DISTINCT FROM b
a IS NOT DISTINCT FROM b

To test whether a value is null, use:

expression IS NULL
expression IS NOT NULL

Or the equivalent nonstandard syntax:

expression ISNULL
expression NOTNULL

Do not write = NULL. NULL is not "equal to" NULL — it represents an unknown value, and two unknown values cannot be compared.

Tip: Some applications expect expression = NULL to return true when the expression evaluates to null. Update those applications to use IS NULL to comply with the SQL standard. If that is not possible, enable the transform_null_equals configuration variable, which converts x = NULL to x IS NULL automatically.

Row-valued expressions

For row-valued expressions, IS NULL is true when the row expression itself is null or when all fields are null. IS NOT NULL is true when the row expression itself is non-null and all fields are non-null. Because of this, IS NULL and IS NOT NULL do not always return inverse results for row-valued expressions — a row with both null and non-null fields returns false for both tests.

To check only whether the overall row value is null, use IS DISTINCT FROM NULL or IS NOT DISTINCT FROM NULL.

Boolean predicates

Boolean predicates always return true or false — never null — even when the operand is null. Null input is treated as the logical value "unknown".

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

IS UNKNOWN and IS NOT UNKNOWN behave the same as IS NULL and IS NOT NULL, except that the input expression must be of boolean type.

Comparison functions

Function	Description	Example
`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`