skip to content

Arrays, Ranges & Domains

Beyond scalars, PostgreSQL offers arrays, range and multirange types, enums, domains, composite rows and inet/cidr. Interviewers ask when an array or range beats a child table and where enums bite.

part ofPostgreSQLread it →
on this pageshow

explore

questions

6

In PostgreSQL, how do you find recipes whose text[] tags column holds one given tag, all of several tags, or any of them?

level: middleimportance: must knowfreq 63%

answer

  1. scalar versus array on each side
  2. contains, contained by, overlaps
  3. order and duplicates ignored
  4. ANY and ALL follow NULL logic
  5. IN is not a membership test here

basics

~20 s

Use 'vegan' = ANY(tags) for one tag, tags @> ARRAY['vegan','quick'] for all of a list, and tags && ARRAY['vegan','quick'] for any of it. Containment and overlap ignore order and duplicates; ANY and ALL follow SQL NULL rules.

solid answer

~40 s

For one tag I write `'vegan' = ANY (tags)`: the scalar is compared with each element. For "has all of these" I use containment, `tags @> ARRAY['vegan','quick']`, and for "has any of these" overlap, `tags && ARRAY['vegan','quick']`; `<@` is the reverse, "every tag is in this list". Containment and overlap treat the arrays as sets, so order and duplicates do not matter, whereas `tags = ARRAY[...]` compares element by element in order. ANY and ALL follow SQL three-valued logic: a NULL element can turn `'nuts' <> ALL (tags)` into NULL. `'vegan' IN (tags)` is not a membership test at all. One practical point: the built-in GIN array operator class indexes `@>`, `<@`, `&&` and `=`, but not `= ANY(column)`, so searches that must use an index are written with `@>`.

code

sql · 14 lines
sql
-- one tag
SELECT name FROM recipe WHERE 'vegan' = ANY (tags);

-- every listed tag, order and duplicates ignored
SELECT name FROM recipe WHERE tags @> ARRAY['vegan', 'quick'];

-- at least one listed tag
SELECT name FROM recipe WHERE tags && ARRAY['vegan', 'gluten-free'];

-- none of the listed allergens (NULL elements do not turn this NULL)
SELECT name FROM recipe WHERE NOT (tags && ARRAY['nuts', 'shellfish']);

-- one bound array parameter instead of a long IN list
SELECT name FROM recipe WHERE id = ANY ($1::bigint[]);

go deeper

for a junior

Know = ANY for one value and that IN does not look inside an array. Be able to write a basic tag search.

for a middle

Name @>, <@ and && with their set semantics, contrast them with ordered array equality, and walk through the NULL rules of ANY and ALL.

for a senior

Show which forms an index can serve, rewrite = ANY(column) as @>, and explain the row-level costs of editing tags held in an array.

for a principal

Weigh array columns against child tables per access pattern: no element foreign keys and whole-row writes versus join-free reads, and set the team convention.

## Four questions, four operators A recipe site stores ingredient and diet tags in `tags text[]`. Most searches are one of four questions, and PostgreSQL has a specific operator for each: | Question | Expression | True when | |---|---|---| | Has this one tag | `'vegan' = ANY (tags)` | some element equals `'vegan'` | | Has all of these tags | `tags @> ARRAY['vegan','quick']` | every listed tag appears in `tags` | | Has at least one of these | `tags && ARRAY['vegan','gluten-free']` | the arrays share any element | | Uses only these tags | `tags <@ ARRAY['vegan','quick','cheap']` | every element of `tags` is in the list | `@>` (contains), `<@` (is contained by) and `&&` (overlaps) take an array on **both** sides. `ANY` and `ALL` take a **scalar** on the left and an array on the right, combined with any boolean operator: `=`, `<>`, `<`, `LIKE` and so on. ## ANY and ALL: a scalar against every element `expr op ANY (array)` is true if any comparison is true; `expr op ALL (array)` is true if every comparison is true. The edge rules come straight from SQL's three-valued logic: - An **empty array** makes `ANY` false and `ALL` true. - A **NULL array** makes both NULL. - A **NULL element** turns a would-be false `ANY` into NULL, and a would-be true `ALL` into NULL. So on a recipe whose tags are `{vegan,NULL}`, `'nuts' <> ALL (tags)` is NULL, and a `WHERE` clause drops that recipe even though it has no nuts tag. `ANY` also replaces long `IN` lists from application code: `WHERE id = ANY ($1::bigint[])` binds one array parameter, so the statement text stays the same whatever the list length. ## Containment and overlap: set semantics on an ordered type An array is ordered and may hold duplicates, but `@>`, `<@` and `&&` ignore both. The manual's example is `ARRAY[1,4,3] @> ARRAY[3,1,3]`, which is true: each element on the right only has to equal **some** element on the left. Containment assumes a strict comparison, so a NULL element never matches anything: `ARRAY['a', NULL] @> ARRAY[NULL]::text[]` is false. Array equality is the opposite. `=` compares element by element in order (and dimensions too), so `ARRAY['vegan','quick'] = ARRAY['quick','vegan']` is false. Using `=` for "has both tags" is a classic wrong-results bug. ## Traps that return the wrong rows 1. **`'vegan' IN (tags)`** compares the value with the whole array, not with its elements; with a literal it fails trying to read `'vegan'` as an array. Use `= ANY`. 2. **`tags = ARRAY[...]`** means "exactly these elements in exactly this order", not "contains". 3. **Mismatched element types.** `@>` and `&&` are defined for two arrays of the same type, so a `varchar[]` column compared with `ARRAY['vegan']` (a `text[]`) can fail with `operator does not exist`; keep tag columns `text[]` or cast the literal to the column's type. 4. **Negations and NULLs.** `NOT ('nuts' = ANY (tags))` and `'nuts' <> ALL (tags)` both go NULL on a NULL element or a NULL array. `NOT (tags @> ARRAY['nuts'])` ignores NULL elements, and a `NOT NULL DEFAULT '{}'` column removes the NULL-array case. ## Making it fast The built-in GIN operator class for arrays supports `<@`, `@>`, `=` and `&&`. The scalar form `'vegan' = ANY (tags)` is not in that list, so a GIN index on `tags` cannot serve it; write `tags @> ARRAY['vegan']` when the query must use the index. Choosing and maintaining that index belongs with GIN indexing; the operator choice is what decides whether it can be used at all. ## When an array column is the right shape The theory of atomic values is first-normal-form territory; the PostgreSQL mechanics are what decide in practice: - **Fits:** a short, read-mostly label set that is written together with its recipe and searched with containment or overlap. One row, no join. - **Hurts:** PostgreSQL has **no foreign key on array elements**, so tags cannot be checked against a `tag` table. - **Hurts:** changing one tag writes a new version of the **whole row**, and two editors changing tags on the same recipe conflict on that row. - **Hurts:** per-tag attributes such as who added it and when have nowhere to live. When those costs dominate, a `recipe_tag` child table is the better design.

  • Why can 'nuts' <> ALL (tags) exclude a recipe that has no nuts tag?
    If `tags` holds a NULL element and no element equals `'nuts'`, every comparison is true or NULL, so `ALL` yields NULL rather than true and `WHERE` drops the row. A NULL `tags` value does the same. `NOT (tags @> ARRAY['nuts'])` skips NULL elements, and a `NOT NULL DEFAULT '{}'` column removes the NULL-array case.
  • When is a tags array a better fit than a recipe_tag child table in PostgreSQL, and when is it worse?
    An array fits a short, read-mostly label set written with its recipe and searched with `@>` or `&&`: one row, no join. It is worse when tags must reference a `tag` table, because PostgreSQL has no foreign key on array elements; when single tags change often, because each change writes a new version of the whole row; or when tags need their own attributes.
  • Does id = ANY($1::bigint[]) behave like an IN list?
    For matching, yes: `id = ANY(ARRAY[1,2,3])` returns the same rows as `id IN (1,2,3)`, and a B-tree index on `id` serves both. The array form binds one parameter whatever the list length, so a driver's prepared statement text stays constant. The negated forms share a NULL hazard: if the list holds a NULL, `id <> ALL(...)` and `NOT IN` can never return true.

  • WHERE 'vegan' IN (tags) checks whether the array contains vegan.
  • tags = ARRAY['vegan','quick'] finds recipes carrying both tags in any order.
  • tags @> ARRAY['vegan','vegan'] only matches recipes that list vegan twice.
  • 'nuts' <> ALL (tags) is always true when no element equals nuts.
  • A GIN index on tags makes 'vegan' = ANY (tags) use the index.
open as a page

In PostgreSQL, how do you add a new value to an enum type like order_status, and what can you not do to it afterwards?

level: middleimportance: must knowfreq 56%

basics

~20 s

ALTER TYPE order_status ADD VALUE 'refunded' adds a label, optionally BEFORE or AFTER another, without rewriting any table; inside a transaction block it is unusable until commit. Labels can be renamed but not removed or reordered.

open as a page

In PostgreSQL, where do array subscripts start, and what does reading tags[0] or a subscript past the end return?

level: juniorimportance: should knowfreq 46%

basics

~10 s

PostgreSQL array subscripts start at 1 by default. Reading tags[0] or any subscript beyond the bounds returns NULL instead of raising an error, so zero-based off-by-one bugs fail silently.

open as a page

In PostgreSQL, what does a domain with a CHECK constraint give you over repeating the CHECK on every column, and what goes wrong when you tighten it on a large schema?

level: seniorimportance: should knowfreq 32%

basics

~20 s

A domain is a named type whose CHECK runs wherever a value is converted to it, so one rule serves every column and function. Tightening it scans every column of that domain under SHARE locks that block writes.

open as a page

In PostgreSQL, why store a shop's opening hours as tstzrange or tstzmultirange rather than opens_at and closes_at columns, and where does a single range run out?

level: seniorimportance: should knowfreq 38%

basics

~20 s

A tstzrange is one value that records its own bound inclusivity, so containment, overlap and adjacency become single operators (@>, &&, -|-) that handle edges and open ends. One range cannot hold a gap; a lunch break needs a tstzmultirange.

open as a page

In PostgreSQL, why store visitor IP addresses as inet rather than text, and when would you use cidr instead?

level: middleimportance: nice to knowfreq 22%

basics

~20 s

inet validates IPv4 and IPv6 input, stores an address with an optional netmask, sorts numerically and supports subnet operators such as <<=. cidr holds only networks and rejects input with bits set right of the mask.

open as a page