Comparison functions and operators

Comparison operators

Operator	Expression & Description	Example
=	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 <br/>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

Operator	Expression & Description	Example
IS DISTINCT FROM	operand1 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 FROM	operand1 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 TRUE	boolean IS TRUE Whether a boolean expression is true.	true IS TRUE → t null::boolean IS TRUE → f
IS NOT TRUE	boolean IS NOT TRUE Whether a boolean expression is false or unknown.	true IS NOT TRUE → f null::boolean IS NOT TRUE → t
IS FALSE	boolean IS FALSE Whether a boolean expression is false.	true IS FALSE → f null::boolean IS FALSE → f
IS NOT FALSE	boolean IS NOT FALSE Whether a boolean expression is true or unknown.	true IS NOT FALSE → t null::boolean IS NOT FALSE → t
IS NULL	value IS NULL Whether a value is null.	1 IS NULL → f
IS NOT NULL	value IS NOT NULL Whether a value is not null.	1 IS NOT NULL → t
IS UNKNOWN	boolean 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 UNKNOWN	boolean IS NOT UNKNOWN Whether a boolean expression returns true or false.	true IS NOT UNKNOWN → t null IS NOT UNKNOWN → f

Special Comparison

secure_compare

Compare two strings in a fixed amount of time, regardless of whether they are equal or not, ensuring that the comparison is secure and resistant to timing attacks.

Syntax

secure_compare (varchar, varchar) -> boolean