Skip to main content

Comparison functions and operators

Comparison operators

OperatorExpression & DescriptionExample
=operand1 = operand2
Equal.
TRUE if the operands separated by = have the same value.
1 = 1 → t
'1' = 1 → t
'a' = 'b' → f
(1, 0) = (1, 1) → f
('a', 'b') = ('a', 'b') → t
<>
!=
operand1 <> operand2 or operand1 != operand2
Not equal.
TRUE if the operands separated by <> or != have different values.
1 <> 1 → f
'1' != 1 → f
'a' != 'b' → t
(1, 0) <> (1, 1) → t
('a', 'b') != ('a', 'b') → f
<operand1 < operand2
Less than.
TRUE if operand1 is less than operand2.
0 < 1 → t
1 < 1 → f
<=operand1 <= operand2
Less than or equal to.
TRUE if operand1 is less than or equal to operand2.
1 <= 1 → t
1 <= 0 → f
>operand1 > operand2
Greater than.
TRUE if operand1 is greater than operand2.
1 > 0 → t
1 > 1 → f
>=operand1 >= operand2
Greater than or equal to.
TRUE if operand1 is greater than or equal to operand2.
1 >= 1 → t
0 >= 1 → f

Comparison predicate

OperatorExpression & DescriptionExample
IS DISTINCT FROMoperand1 IS DISTINCT FROM operand2
Not equal (null comparable).
TRUE if operand1 is not equal to operand2.
1 IS DISTINCT FROM NULL → t
1 IS DISTINCT FROM 1 → f
IS NOT DISTINCT FROMoperand1 IS NOT DISTINCT FROM operand2
Equal (null comparable).
TRUE if operand1 is equal to operand2.
1 IS NOT DISTINCT FROM NULL → f
BETWEEN ... AND ...operand BETWEEN min AND max
Between (inclusive range).
TRUE if the operand is greater than or equal to min and less than or equal to max.
1 BETWEEN 0 AND 1 → t
'c' BETWEEN 'a' AND 'b' → f
NOT BETWEEN ... AND ...operand NOT BETWEEN min AND max
Not between (inclusive range).
TRUE if the operand is less than min and greater than max.
1 NOT BETWEEN 0 AND 1 → f
IN()operand IN (value,...)
Whether a value is equal to any of the values you specify.
TRUE if the operand is equal to one of the specified expressions/values. NULL if the operand is null or if the operand is not in the specified expressions/values that contain a null.
1 IN (0,1,2,3) → t
'a' IN ('ab','b','c','d') → f
null IN (null, 3, 0.5*2, min(v1)) → NULL
99 IN (null, 3, 2) → NULL
NOT IN()operand NOT IN (value,...)
Whether a value is not equal to any of the values you specify.
TRUE if the operand is not equal to any specified expressions/values.
1 NOT IN (0,1,2,3) → f
ANY()expression operator ANY (array expression)
Compares a value with the element in a set or array of values.
The right-hand side is a parenthesized expression and must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result.

TRUE if any true result is obtained. FALSE if no true result is found (including the case where the array has zero elements).

NULL if the array expression yields a null array, or if the left-hand expression yields null (assuming a strict comparison operator), or if the right-hand array contains any null elements and no true comparison result is obtained (assuming a strict comparison operator).
5 = ANY(ARRAY[1, 3, 5, 7]) → t
SOME()expression operator SOME (array expression)
SOME is a synonym for ANY.
5 = SOME(ARRAY[1, 3, 5, 7]) → t
ALL()expression operator ALL (array expression)
Compares a value with the element in a set or array of values.
The right-hand side is a parenthesized expression and must yield an array value. The left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result.

TRUE if all comparisons yield true (including the case where the array has zero elements). FALSE if any false result is found.

NULL if the array expression yields a null array, or if the left-hand expression yields null (assuming a strict comparison operator), or if the right-hand array contains any null elements and no false comparison result is obtained(assuming a strict comparison operator).
5 > ALL(ARRAY[1, 3, 5, 7]) → f
IS TRUEboolean IS TRUE
Whether a boolean expression is true.
true IS TRUE → t
null::boolean IS TRUE → f
IS NOT TRUEboolean IS NOT TRUE
Whether a boolean expression is false or unknown.
true IS NOT TRUE → f
null::boolean IS NOT TRUE → t
IS FALSEboolean IS FALSE
Whether a boolean expression is false.
true IS FALSE → f
null::boolean IS FALSE → f
IS NOT FALSEboolean IS NOT FALSE
Whether a boolean expression is true or unknown.
true IS NOT FALSE → t
null::boolean IS NOT FALSE → t
IS NULLvalue IS NULL
Whether a value is null.
1 IS NULL → f
IS NOT NULLvalue IS NOT NULL
Whether a value is not null.
1 IS NOT NULL → t
IS UNKNOWNboolean IS UNKNOWN
Whether a boolean expression returns an unknown value (typically represented by a null).
null IS UNKNOWN → t
false IS UNKNOWN → f
IS NOT UNKNOWNboolean IS NOT UNKNOWN
Whether a boolean expression returns true or false.
true IS NOT UNKNOWN → t
null IS NOT UNKNOWN → f

Help us make this doc better!