PostgreSQL: Row and Array Comparisons
I kind of skipped over the functions/operators section of the PostgreSQL documentation when reading it. I want to review the sections that I think would be useful to me.
References
Notes
This section describes specialized constructs for making comparisons between groups of values. These forms are syntactically involved to subquery forms, but do not involve subqueries.
IN
expression IN (value [, ...])
The right hand side is a parenthesized list of expressions. The result is true
if the left-hand expression's result is equal to any of the right-hand expressions. If the left-hand expression yields null, or if there are no equal right-hand values and at least one right-hand expression yields null, the result of the IN construct will be null, not false. This is in accordance with SQL's normal rules for Boolean combinations of null values.
NOT IN
expression NOT IN (value, [...])
The right hand side is a parenthesized list of expressions. The result is true
if the left-hand expression's result is unequal to all of the right-hand expressions.
ANY/SOME (array)
expresison operator ANY (array expression)
expression operator SOME (array expression)
The right hand side is a parenthesized expression, which 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. The result of ANY is true
if any true result is obtained. The result is false
if no true result is found (including the case where the array has zero elements). If the array expression yields a null array, the result of ANY will be null. If the left-hand expression yields null, the result of ANY is ordinarily null. SOME is a synonym for ANY
ALL (array)
expression operator ALL (array expression)
The right hand side is a parenthesized expression, which 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. The result of ALL is true
if all comparisons yield true (including the case where the array has zero elements). The result if false
if any false result is found.
Row Constructor Comparison
row_constructor operator row_constructor Each side is a row constructor. The two row constructors must have the same number of fields. The given operator is applied to each pair of corresponding fields. All the selected operators must be members of some B-tree operator class, or be the negator of an = member of a B-tree operator class, meaning that row constructor comparison is only possible when the operator is =, <>, <=, <, >, or >=, or something similar.
row_constructor IS DISTINCT FROM row_constructor
The constructor is similar to a <> row comparison, but it does not yield null for null inputs.
row_constructor IS NOT DISTINCT FROM row_constructor
This constant is similar to a = row comparison, but it does not yield null for null inputs.
Composite Type Comparison
The SQL specification requires row-wise comparison to return NULL if the result on comparing two NULL values or a NULL and a non-NULL. PostgreSQL does this only when comparing the results of two constructors or comparing a row constructor of the output of a subquery. In other contexts where two composite-type values are compared, two NULLL field values are considered equal, and a NULL is considered larger than a non-NULL. This is necessary in order to have consistent sorting and indexing behavior for composite types.
Each side is evaluated and they are compared row-wise.
Comments
You have to be logged in to add a comment
User Comments
There are currently no comments for this article.