Arrays, ANY/ALL, Unnesting, Multidimensional Semantics, and Modeling Boundaries

Learning outcomes

Arrays are a genuine PostgreSQL type, not serialized text. They preserve element type, dimensions, optional non-1 lower bounds, NULL elements, and multidimensional shape. They are excellent for compact value collections that naturally belong to one row—but poor substitutes for many-to-many relationships that require foreign keys, per-element attributes, or independent lifecycle.

1. Build a typed array lab

DROP TABLE IF EXISTS app.ch17_array_work_order CASCADE;CREATE TABLE app.ch17_array_work_order (  work_order_id bigint PRIMARY KEY,  tags text[] NOT NULL DEFAULT '{}'::text[],  readings integer[],  calibration_matrix numeric[][]);INSERT INTO app.ch17_array_work_order VALUES(17301, ARRAY['urgent','pump'], ARRAY[41,42,NULL,44], ARRAY[[1.0,0.1],[0.0,1.0]]),(17302, ARRAY['routine'],       ARRAY[]::integer[],    ARRAY[[1.0,0.0],[0.2,1.0]]),(17303, ARRAY[]::text[],        NULL,                  NULL);

PostgreSQL array declarations can be multidimensional, but the declared dimensionality is documentation rather than a fixed shape constraint. If the application requires a 2×2 calibration matrix, enforce that shape with a CHECK rather than assuming numeric[][] guarantees it.

2. Dimensions and lower bounds are part of the value

SELECT work_order_id,       array_dims(readings) AS dims,       array_lower(readings,1) AS lower_1,       array_upper(readings,1) AS upper_1,       cardinality(readings) AS elementsFROM app.ch17_array_work_orderORDER BY work_order_id;SELECT array_dims('[0:2]={10,20,30}'::integer[]) AS non_one_based_dims,       ('[0:2]={10,20,30}'::integer[])[0] AS first_element;

Arrays can start at a subscript other than 1. Client code that assumes one-based bounds can be wrong even though most constructor syntax creates arrays starting at 1.

SELECT calibration_matrix,       array_dims(calibration_matrix),       cardinality(calibration_matrix)FROM app.ch17_array_work_orderWHERE work_order_id = 17301;SELECT value, ordinalityFROM unnest(ARRAY[[1,2],[3,4]]) WITH ORDINALITY AS u(value, ordinality);

unnest reads elements in storage (row-major) order. WITH ORDINALITY adds a simple 1-based output sequence; it is not the same thing as reconstructing every original multidimensional subscript.

3. ANY and ALL follow SQL three-valued logic

SELECT  42 = ANY(ARRAY[41,42,43])       AS any_true,  42 = ANY(ARRAY[]::integer[])    AS any_empty_false,  42 = ALL(ARRAY[]::integer[])    AS all_empty_true,  42 = ANY(NULL::integer[])       AS any_null_array,  42 = ANY(ARRAY[NULL,7]::integer[]) AS any_no_match_with_null,  42 = ANY(ARRAY[NULL,42]::integer[]) AS any_match_despite_null;

ANY(empty) is false because no comparison is true. ALL(empty) is true because no comparison is false. A NULL array yields NULL, and a NULL element can make the result unknown when no decisive true/false result exists.

4. Unnest values while preserving list order

SELECT w.work_order_id, u.ordinality, u.tagFROM app.ch17_array_work_order AS wCROSS JOIN LATERAL unnest(w.tags) WITH ORDINALITY AS u(tag, ordinality)ORDER BY w.work_order_id, u.ordinality;

Empty arrays produce zero unnested rows. A NULL array also produces zero rows through unnest(NULL); if the distinction matters, preserve a separate tags IS NULL signal before unnesting.

5. GIN supports array operators, not arbitrary array expressions

CREATE INDEX ch17_array_work_order_tags_ginON app.ch17_array_work_order USING GIN (tags);EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT work_order_idFROM app.ch17_array_work_orderWHERE tags @> ARRAY['urgent'];EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, SUMMARY ON)SELECT work_order_idFROM app.ch17_array_work_orderWHERE tags && ARRAY['urgent','vip'];

The built-in GIN array_ops operator class indexes @>, <@, &&, and array equality. The natural-looking predicate 'urgent' = ANY(tags) has membership semantics, but it is not the same index operator signature. If array containment is semantically equivalent for your case, write tags @> ARRAY['urgent'] to match the GIN contract.

6. Enforce shape only when shape is a real invariant

ALTER TABLE app.ch17_array_work_orderADD CONSTRAINT ch17_calibration_2x2CHECK (  calibration_matrix IS NULL  OR (    array_ndims(calibration_matrix) = 2    AND array_length(calibration_matrix,1) = 2    AND array_length(calibration_matrix,2) = 2  ));

This is a reasonable array invariant: the values form one atomic calibration matrix owned by the row. It differs from a changing set of related technicians, which has independent identity and relationship attributes.

7. Wrong model: an array of foreign identifiers

DROP TABLE IF EXISTS app.ch17_work_order_bad;CREATE TABLE app.ch17_work_order_bad (  work_order_id bigint PRIMARY KEY,  technician_ids bigint[] NOT NULL DEFAULT '{}'::bigint[]);INSERT INTO app.ch17_work_order_badVALUES (1, ARRAY[7001,7002,999999]);

PostgreSQL cannot declare a normal foreign key that validates each element of technician_ids against a technicians table. Adding relationship attributes such as role, assigned_at, or primary/backup status becomes awkward, and joins require repeated unnesting.

8. Repair: model the relationship as rows

DROP TABLE IF EXISTS app.ch17_work_order_technician;DROP TABLE IF EXISTS app.ch17_technician;CREATE TABLE app.ch17_technician (  technician_id bigint PRIMARY KEY,  display_name text NOT NULL);CREATE TABLE app.ch17_work_order_technician (  work_order_id bigint NOT NULL    REFERENCES app.ch17_work_order_bad(work_order_id) ON DELETE CASCADE,  technician_id bigint NOT NULL    REFERENCES app.ch17_technician(technician_id),  assignment_role text NOT NULL CHECK (assignment_role IN ('primary','backup')),  assigned_at timestamptz NOT NULL DEFAULT clock_timestamp(),  PRIMARY KEY (work_order_id, technician_id));INSERT INTO app.ch17_technician VALUES(7001,'Ava'), (7002,'Sam');INSERT INTO app.ch17_work_order_technician(work_order_id, technician_id, assignment_role)VALUES(1,7001,'primary'),(1,7002,'backup');-- This now fails instead of silently accepting a nonexistent technician:INSERT INTO app.ch17_work_order_technician(work_order_id, technician_id, assignment_role)VALUES (1,999999,'backup');

Now each relationship has referential integrity, uniqueness, attributes, statistics, and ordinary indexes. Arrays remain valuable for true row-owned collections such as tags, compact measurements, or matrix-like data.

9. Checkpoint

Authoritative references

These data types and index/operator contracts are version-sensitive. The lesson uses the PostgreSQL 18 primary documentation below.

• PostgreSQL 18 — Arrays
• PostgreSQL 18 — Array Functions and Operators
• PostgreSQL 18 — ANY/ALL Array Comparisons
• PostgreSQL 18 — GIN Operator Classes