---
source: https://iinventedlight.com/library/notes/postgres-arrays.md
title: "PostgreSQL array columns and functions"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 8.15. Arrays, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 9.19. Array Functions and Operators, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: PostgreSQL"
tags: postgresql, arrays, sql
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 8.15. Arrays"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/arrays.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: 9.19. Array Functions and Operators"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/functions-array.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# PostgreSQL array columns and functions

## 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]

```sql
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).