≡

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

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

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.

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

Parameter	Type	Description
expression	any	Field 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

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

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

Parameter	Type	Description
array1	anyarray	Left-hand array
array2	anyarray	Right-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

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

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

Parameter	Type	Description
row1	record	Left-hand row
row2	record	Right-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

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

PostgreSQL Row & Array Comparison Operators

Jump to function

What are PostgreSQL Row & Array Comparison Operators?

ROW() constructor

Signature

Parameters

Examples

Array comparisons (= ANY, @>)

Signatures

Parameters

Examples

Row comparison operators

Signatures

Parameters

Examples

Related PostgreSQL Categories