Use comparison functions and operators - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for PostgreSQL is compatible with the comparison functions and operators of PostgreSQL. This topic describes the comparison functions and operators that are supported by AnalyticDB for PostgreSQL and provides examples on how to use the comparison functions and operators.

For information about the comparison functions and operators of PostgreSQL, see Comparison Functions and Operators.

Comparison operators

Overview

Comparison operators are specific symbols that are used for comparison, such as =, !=, <, >, <=, and >=. Comparison operators can be applied to expressions. A comparison operator compares two values and returns a Boolean value of TRUE or FALSE to indicate the comparison result.

Usage

Comparison operators can be used in conditional statements (such as IF conditions), loop control, and SQL queries to determine how data is processed during filtering and sorting.

Operators

Comparison operators

Operator	Description
`<`	Less than
`>`	Greater than
`<=`	Less than or equal to
`>=`	Greater than or equal to
`=`	Equal to
`<>or!=`	Not equal to

Comparison predicates

Comparison predicates function similarly to operators but must meet specific syntax requirements of the SQL standard.

Predicate	Description
`aBETWEENxANDy`	Checks whether a is between `x` and `y`.
`aNOT BETWEENxANDy`	Checks whether a is not between `x` and `y`.
`aBETWEEN SYMMETRICxANDy`	Checks whether a is between `x` and `y`. x and y are swapped if the range from x to y is empty.
`aNOT BETWEEN SYMMETRICxANDy`	Checks whether a is not between `x` and `y`. x and y are swapped if the range from x to y is empty.
`aIS DISTINCT FROMb`	Checks whether a and b are different. This predicate treats a NULL value as an ordinary value.
`aIS NOT DISTINCT FROMb`	Checks whether a and b are identical. This predicate treats a NULL value as an ordinary value.
`expressionIS NULL`	Checks whether an expression is NULL.
`expressionIS NOT NULL`	Checks whether an expression is not NULL.
`expressionISNULL`	Checks whether an expression is NULL. This predicate uses a non-standard syntax.
`expressionNOTNULL`	Checks whether an expression is not NULL. This predicate uses a non-standard syntax.
`boolean_expressionIS TRUE`	Checks whether a Boolean expression is TRUE.
`boolean_expressionIS NOT TRUE`	Checks whether a Boolean expression is not TRUE (FALSE or UNKNOWN).
`boolean_expressionIS FALSE`	Checks whether a Boolean expression is FALSE.
`boolean_expressionIS NOT FALSE`	Checks whether a Boolean expression is not FALSE (TRUE or UNKNOWN).
`boolean_expressionIS UNKNOWN`	Checks whether a Boolean expression is UNKNOWN.
`boolean_expressionIS NOT UNKNOWN`	Checks whether a Boolean expression is not UNKNOWN (TRUE or FALSE).

Comparison functions

Overview

Comparison functions are used to compare field values. For example, you can use the strcmp() function to compare strings or customize comparison functions. Comparison functions allow you to specify multiple parameters and custom comparison logic in a more flexible manner.

Usage

Comparison functions are useful in scenarios that involve complex comparison logic, such as performing case-insensitive string comparisons, comparing specific attributes of objects, and customizing sorting rules.

Functions

Function	Description	Example	Sample result
`num_nonnulls(VARIADIC "any")`	Returns the number of non-NULL arguments.	`num_nonnulls(1, NULL, 2)`	`2`
`num_nulls(VARIADIC "any")`	Returns the number of NULL arguments.	`num_nulls(1, NULL, 2)`	`1`