9.23. Row and Array Comparisons
This section describes several specialized constructs for making multiple comparisons between groups of values. These forms are syntactically related to the subquery forms of the previous section, but do not involve subqueries. The forms involving array subexpressions arePostgres Pro extensions; the rest areSQL-compliant. All of the expression forms documented in this section return Boolean (true/false) results.
row_constructoroperatorrow_constructor
Each side is a row constructor, as described inSection 4.2.13. The two row values must have the same number of fields. Each side is evaluated and they are compared row-wise. Row constructor comparisons are allowed when theoperator is=,<>,<,<=,> or>=. Every row element must be of a type which has a default B-tree operator class or the attempted comparison may generate an error.
Note
Errors related to the number or types of elements might not occur if the comparison is resolved using earlier columns.
The= and<> cases work slightly differently from the others. Two rows are considered equal if all their corresponding members are non-null and equal; the rows are unequal if any corresponding members are non-null and unequal; otherwise the result of the row comparison is unknown (null).
For the<,<=,> and>= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null); otherwise comparison of this pair of elements determines the result. For example,ROW(1,2,NULL) < ROW(1,3,0) yields true, not null, because the third pair of elements are not considered.
Note
Prior toPostgreSQL 8.2, the<,<=,> and>= cases were not handled per SQL specification. A comparison likeROW(a,b) < ROW(c,d) was implemented asa < c AND b < d whereas the correct behavior is equivalent toa < c OR (a = c AND b < d).
row_constructorIS DISTINCT FROMrow_constructor
This construct is similar to a<> row comparison, but it does not yield null for null inputs. Instead, any null value is considered unequal to (distinct from) any non-null value, and any two nulls are considered equal (not distinct). Thus the result will either be true or false, never null.
row_constructorIS NOT DISTINCT FROMrow_constructor
This construct is similar to a= row comparison, but it does not yield null for null inputs. Instead, any null value is considered unequal to (distinct from) any non-null value, and any two nulls are considered equal (not distinct). Thus the result will always be either true or false, never null.
9.23.6. Composite Type Comparison
recordoperatorrecord
The SQL specification requires row-wise comparison to return NULL if the result depends on comparing two NULL values or a NULL and a non-NULL.Postgres Pro does this only when comparing the results of two row constructors (as inSection 9.23.5) or comparing a row constructor to the output of a subquery (as inSection 9.22). In other contexts where two composite-type values are compared, two NULL 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. Composite type comparisons are allowed when theoperator is=,<>,<,<=,> or>=, or has semantics similar to one of these. (To be specific, an operator can be a row comparison operator if it is a member of a B-tree operator class, or is the negator of the= member of a B-tree operator class.) The default behavior of the above operators is the same as forIS [ NOT ] DISTINCT FROM for row constructors (seeSection 9.23.5).
To support matching of rows which include elements without a default B-tree operator class, the following operators are defined for composite type comparison:*=,*<>,*<,*<=,*>, and*>=. These operators compare the internal binary representation of the two rows. Two rows might have a different binary representation even though comparisons of the two rows with the equality operator is true. The ordering of rows under these comparison operators is deterministic but not otherwise meaningful. These operators are used internally for materialized views and might be useful for other specialized purposes such as replication but are not intended to be generally useful for writing queries.