← All notes

Short answer

Any column can be a variable-length, multidimensional array of almost any type. Write literals as '{1,2,3}' or ARRAY[1,2,3]; arrays are one-based, so the first element is a[1]. [S1]

SELECT tags[1], array_length(tags, 1) FROM posts;
SELECT unnest(ARRAY['a','b']);          -- one row per element
UPDATE posts SET tags = array_append(tags, 'new');

[S2]

Gotchas

• Also, null is returned if a subscript is outside the array bounds (this case does not raise an error). (S1)

Claims

• id: C1 claim: Columns can be variable-length multidimensional arrays. source: S1 evidence: "defined as variable-length multidimensional arrays"
• id: C2 claim: PostgreSQL arrays use one-based numbering by default. source: S1 evidence: "By default PostgreSQL uses a one-based numbering convention for arrays"
• id: C3 claim: array_length takes an array and a dimension. source: S2 evidence: "array_length ( anyarray, integer ) → integer"
• id: C4 claim: unnest expands an array to a set of rows. source: S2 evidence: "unnest ( anyarray ) → setof anyelement"

Sources

• [S1] PostgreSQL: Documentation: 18: 8.15. Arrays. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/arrays.html . License: PostgreSQL License. Accessed 2026-11-04.
• [S2] PostgreSQL: Documentation: 18: 9.19. Array Functions and Operators. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/functions-array.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_wp1b93kn,
  author = {{iinventedlight}},
  title = {{PostgreSQL array columns and functions}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-arrays/},
  note = {Permanent ID iil:note:wp1b93kn. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-arrays.md.