skip to content

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%

answer

  1. the SQL counting convention
  2. out of bounds is quiet
  3. an array can carry its own lower bound
  4. empty array: two length functions disagree
  5. unnest WITH ORDINALITY for positions

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.

solid answer

~30 s

PostgreSQL arrays are one-based by default, so `tags[1]` is a recipe's first tag. An out-of-range read such as `tags[0]` or `tags[99]`, or a read with the wrong number of subscripts, returns `NULL` rather than an error, which is why a zero-based habit fails silently. One-based is only the default: a value like `'[0:2]={a,b,c}'` carries its own lower bound, so robust code asks `array_lower`/`array_upper` or iterates with `unnest(tags) WITH ORDINALITY`. For size, `cardinality(tags)` returns 0 for an empty array while `array_length(tags, 1)` returns `NULL`. A declared size like `text[5]` is not enforced. PostgreSQL 18 adds `array_sort()` and `array_reverse()`.

code

sql · 20 lines
sql
CREATE TABLE recipe (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name text NOT NULL,
  tags text[] NOT NULL DEFAULT '{}'
);
INSERT INTO recipe (name, tags)
VALUES ('Shakshuka', ARRAY['vegetarian', 'eggs', 'one-pan']);

SELECT tags[1]           AS first_tag,  -- vegetarian
       tags[0]           AS zero,       -- NULL, no error
       tags[9]           AS past_end,   -- NULL, no error
       tags[2:3]         AS slice,      -- {eggs,one-pan}
       cardinality(tags) AS n,          -- 3
       array_sort(tags)  AS sorted      -- {eggs,one-pan,vegetarian} (18)
FROM recipe
WHERE name = 'Shakshuka';

SELECT t.tag, t.pos
FROM recipe AS r, unnest(r.tags) WITH ORDINALITY AS t(tag, pos)
WHERE r.name = 'Shakshuka';

go deeper

for a junior

Recall that subscripts start at 1 and that tags[0] quietly returns NULL. Know cardinality() as the safe way to count elements.

for a middle

Explain the empty-array case: zero dimensions, so array_length, array_upper and array_ndims return NULL. Show unnest WITH ORDINALITY for position-aware iteration.

for a senior

Point out that silent NULLs from bad subscripts hide bugs in reports, that lower bounds can move after assignment, and that declared sizes need a CHECK or domain to mean anything.

for a principal

Frame arrays as a convenience with loose guarantees: decide which invariants (size, no NULL elements, bounds) the schema must enforce and which code may assume.

## The convention: one-based by default PostgreSQL numbers array elements from **1**, as the SQL standard does. For a recipe row whose `tags` column is `text[]` holding `{vegetarian,eggs,one-pan}`, `tags[1]` is `vegetarian` and `tags[3]` is `one-pan`. Developers who think in zero-based languages write `tags[0]` out of habit, and PostgreSQL does not stop them: the expression is legal and simply finds nothing. ## Out-of-bounds reads return NULL, not an error The manual is explicit that a subscript outside the array bounds returns `NULL` and **does not raise an error**. The same happens when the array itself is `NULL`, when a subscript expression is `NULL`, or when you use the wrong number of subscripts (`tags[1][1]` on a one-dimensional array). Slices follow slightly different rules, kept for historical reasons: | Expression on `{vegetarian,eggs,one-pan}` | Result | |---|---| | `tags[1]` | `vegetarian` | | `tags[0]` | `NULL` | | `tags[9]` | `NULL` | | `tags[2:3]` | `{eggs,one-pan}` | | `tags[2:9]` | `{eggs,one-pan}`, trimmed to the overlap | | `tags[7:9]` | `{}`, an empty array rather than `NULL` | The practical consequence is that an off-by-one bug produces `NULL`s that flow quietly into a report, or into a `WHERE` clause that then matches nothing. No exception points at the line. ## Arrays that do not start at 1 One-based is a default, not a guarantee. An array literal can carry explicit bounds: `'[0:2]={a,b,c}'::text[]` has subscripts 0 to 2. Subscripted assignment can also move the lower bound: assigning to `tags[0]` on a one-based array extends it downward and produces an array that starts at 0. Such values print with a bounds decoration such as `[0:3]={...}`, and a loop written as `FOR i IN 1..array_length(tags, 1)` now skips element 0 and reads one position past the end. Robust code asks the array instead of assuming: - `array_lower(tags, 1)` and `array_upper(tags, 1)` return the real bounds of dimension 1. - `generate_subscripts(tags, 1)` yields every valid subscript as rows. - `unnest(tags) WITH ORDINALITY` returns each element with a position counted from 1, whatever the stored bounds; `unnest` reads elements in storage order. ## Measuring an array: the empty-array trap An empty array in PostgreSQL has **zero dimensions**, so every function that asks about a dimension returns `NULL` for it: | Function | `{a,b,c}` | `'{}'` | |---|---|---| | `cardinality(tags)` | 3 | 0 | | `array_length(tags, 1)` | 3 | `NULL` | | `array_upper(tags, 1)` | 3 | `NULL` | | `array_ndims(tags)` | 1 | `NULL` | So `WHERE array_length(tags, 1) = 0` never matches an empty array. Write `cardinality(tags) = 0` or `tags = '{}'` instead. `cardinality` also counts every element of a multidimensional array, which `array_length` on one dimension does not. ## Declared sizes and writing past the end A declaration such as `text[5]` or `integer[3][3]` is documentation only: PostgreSQL enforces neither the size nor the number of dimensions, and all arrays of one element type are the same type. To enforce a limit, use a `CHECK (cardinality(tags) <= 20)` or a domain over `text[]`. Assignment past the end of a **one-dimensional** array enlarges it and fills any gap with `NULL`s: setting `tags[5]` on a three-element array yields five elements with `tags[4]` null. This enlargement is not allowed for multidimensional arrays. ## Reordering: array_sort and array_reverse in PostgreSQL 18 Before 18 the idiom for sorting an array value was `ARRAY(SELECT t FROM unnest(tags) AS t ORDER BY t)`. PostgreSQL 18 adds two functions: 1. `array_sort(tags [, descending [, nulls_first]])` sorts the first dimension by the element type's default ordering. Ascending is the default; `nulls_first` defaults to the value of `descending`, so an ascending sort puts `NULL`s last. For text, a `COLLATE` clause on the argument picks the collation. 2. `array_reverse(tags)` reverses the first dimension. For a multidimensional array both act on the first dimension only and move whole sub-arrays: `array_sort(ARRAY[[2,4],[2,1],[6,5]])` returns `{{2,1},{2,4},{6,5}}`. On 17 and earlier, neither function exists and the `unnest` idiom remains the answer. ## A checklist for array code - Count with `cardinality(tags)` and test emptiness with `cardinality(tags) = 0`. - Iterate with `unnest(tags) WITH ORDINALITY` or `generate_subscripts`, never a hard-coded `1..n` loop. - Treat a `NULL` read through a subscript as a sign of a bug, not as data. - Enforce size limits with a `CHECK` or a domain, never with a declared `[n]`.

  • What does array_length(tags, 1) return for an empty array, and why does that bite in a WHERE clause?
    It returns `NULL`, not 0, because an empty PostgreSQL array has no dimensions at all. So `WHERE array_length(tags, 1) = 0` never matches an empty array, and `array_length(tags, 1) < 3` skips empty ones too. Use `cardinality(tags)`, which returns 0, or compare with `'{}'`.
  • A recipe's tags has three elements and you run UPDATE recipe SET tags[5] = 'spicy'. What happens?
    The one-dimensional array is enlarged: position 5 gets `spicy` and position 4 is filled with `NULL`, giving five elements and no error. Assigning to `tags[0]` would instead extend the array downward and leave it with a lower bound of 0. Neither enlargement is allowed on a multidimensional array.
  • In PostgreSQL 18, where does array_sort put NULL elements, and how do you change that?
    By default `array_sort` sorts ascending and puts `NULL`s last. Its optional `nulls_first` argument defaults to the value of `descending`, so `array_sort(tags, true)` sorts descending with `NULL`s first. Pass both booleans to choose independently, for example `array_sort(tags, false, true)` for ascending with `NULL`s first.

  • PostgreSQL arrays are zero-based like most programming languages.
  • Reading tags[10] on a three-element array raises a subscript-out-of-range error.
  • Declaring a column as text[5] stops anyone storing a sixth tag.
  • array_length(tags, 1) returns 0 for an empty array.
  • Every PostgreSQL array starts at 1, so looping from 1 to array_length is always safe.

Assumes PostgreSQL 18; array_sort() and array_reverse() are new in 18, so on 17 and earlier you sort with unnest plus ORDER BY.