PG
PRO
≡

PostgreSQL Row & Array Comparison Operators

Complete reference for PostgreSQL row and array comparison operators including the ROW() constructor for multi-column comparisons, array containment operators @>, <@, and &&, and row equality comparisons. Updated for PostgreSQL 16.

3 functions

Jump to function

Array comparisons (= ANY, @>)Row comparison operatorsROW() constructor

What are PostgreSQL Row & Array Comparison Operators?

PostgreSQL row and array comparison operators compare composite values (rows) and array values as units. The ROW() constructor creates composite values for multi-column IN tests, @> tests if one array contains all elements of another, <@ tests if an array is contained within another, and && tests for any overlap between two arrays. Array containment operators are supported by GIN indexes for efficient set-membership queries.

Array comparisons (= ANY, @>)

PG 8.2+→ boolean

Array containment and overlap operators. @> tests if the left array contains all elements of the right; <@ is the reverse; && tests for any overlap.

DeveloperData Eng

Signatures

anyarray @> anyarray → boolean
anyarray <@ anyarray → boolean
anyarray && anyarray → boolean

Parameters

ParameterTypeDescription
array1anyarrayLeft-hand array
array2anyarrayRight-hand array

Examples

sql
SELECT ARRAY[1,2,3] @> ARRAY[2,3];
→true (contains)
sql
SELECT ARRAY[1,2] <@ ARRAY[1,2,3,4];
→true (is contained by)
sql
SELECT ARRAY[1,2] && ARRAY[3,4];
→false (no overlap)
sql
SELECT * FROM posts WHERE tags @> ARRAY['postgres','performance'];
→Posts tagged with both postgres and performance
⚠Anti-Pattern— Confusing && (overlap) with @> (containment) for array filtering

`ARRAY[1,2,3] && ARRAY[2,9]` is true because 2 is shared — even though 9 is not in the left array. Developers intending 'the left array contains all elements of the right' use `&&` and get false positives.

✓ Instead: Use `col_array @> ARRAY[required1, required2]` to test that the column array contains all required elements. Use `&&` only when partial overlap is the correct condition.

Create a GIN index on array columns to accelerate `@>`, `<@`, and `&&` operators: `CREATE INDEX ON posts USING GIN (tags)`. Without this index, these operators require full table scans.

example
CREATE INDEX ON articles USING GIN (tags);
SELECT * FROM articles WHERE tags @> ARRAY['postgresql', 'index'];
→Fast containment search on GIN-indexed array column
See also:any_operatorunnestarray_positionarray_agg

Row comparison operators

PG 7.4+→ boolean

Compares two row constructor values field by field. Comparison is lexicographic: first field is most significant.

DeveloperData Eng

Signatures

(row) = (row) → boolean
(row) < (row) → boolean
(row) IS DISTINCT FROM (row) → boolean

Parameters

ParameterTypeDescription
row1recordLeft-hand row
row2recordRight-hand row

Examples

sql
SELECT (1, 2) = (1, 2);
→true
sql
SELECT (1, 2) < (1, 3);
→true (second field decides)
sql
SELECT * FROM events WHERE (year, month, day) > (2025, 1, 1);
→Events after January 1, 2025
sql
SELECT * FROM posts WHERE (published_at, id) < ($1, $2) ORDER BY published_at DESC, id DESC LIMIT 20;
→Efficient cursor-based keyset pagination
sql
SELECT (1, NULL) IS NOT DISTINCT FROM (1, NULL);
→true (NULL-safe row equality)
⚠Anti-Pattern— Using ROW comparison instead of individual column predicates on indexed columns

`(status, priority) = ('open', 1)` as a row comparison typically cannot use separate B-tree indexes on `status` or `priority`. The planner treats the composite predicate as a single expression.

✓ Instead: Use `status = 'open' AND priority = 1` to allow the planner to use individual indexes on each column. Reserve row comparisons for keyset pagination (`(ts, id) < ($1, $2)`) where the composite ordering is the point.

Keyset pagination with multiple sort columns is clean with row constructors: `WHERE (created_at, id) < ($last_ts, $last_id) ORDER BY created_at DESC, id DESC LIMIT 20`. This is efficient and handles ties correctly.

example
SELECT * FROM posts WHERE (published_at, id) < ($1, $2) ORDER BY published_at DESC, id DESC LIMIT 20;
→Efficient cursor-based pagination with multi-column sort
See also:row_constructoris_distinct_from

ROW() constructor

PG 7.4+→ record

Constructs an anonymous composite (row) value from a list of field values. ROW keyword is optional when there are multiple fields.

DeveloperData Eng

Signature

ROW ( expression [, expression ...] ) → record

Parameters

ParameterTypeDescription
expressionanyField values for the row

Examples

sql
SELECT ROW(1, 'Alice', true);
→(1,Alice,t)
sql
SELECT (1, 'hello', now()) AS my_row;
→(1,hello,2025-03-07...)
sql
SELECT ROW(id, name) FROM users LIMIT 3;
→Row values as composite records
sql
SELECT * FROM orders WHERE (year, month) IN ((2024, 12), (2025, 1), (2025, 2));
→Orders in specified year-month pairs
sql
INSERT INTO archive SELECT ROW(id, status, updated_at) FROM jobs WHERE done;
→Composite rows inserted into archive
⚠Anti-Pattern— Comparing ROW values containing NULLs with = (returns NULL, not true/false)

`ROW(1, NULL) = ROW(1, NULL)` evaluates to NULL because `NULL = NULL` is NULL. Using row equality with NULLable fields in WHERE or JOIN conditions silently excludes or produces wrong results.

✓ Instead: Use `(a, b) IS NOT DISTINCT FROM (x, y)` for NULL-safe row equality comparisons, especially when any field in the composite can be NULL.

You can compare row constructors: `(col1, col2) = (val1, val2)` is equivalent to `col1 = val1 AND col2 = val2` but more concise. Also useful for multi-column IN checks: `(a,b) IN ((1,2),(3,4))`.

example
SELECT * FROM orders WHERE (year, month) IN ((2024, 12), (2025, 1), (2025, 2));
→Orders in specific year-month combinations
See also:row_to_jsoncomposite_typeunnest

Related PostgreSQL Categories

Array Functions →Comparison Operators →Composite Types →