Array | PostgreSQL Tutorial

Introduction to PostgreSQL arrays

PostgreSQL allows columns to store multiple values of the same type in a single array. Every data type has a companion array type: integer[], text[], boolean[], and so on. Arrays are useful for storing multi-valued attributes like phone numbers, tags, or scores without requiring a separate table.

Define a one-dimensional array column with:

column_name datatype[]

Creating a table with an array column

CREATE TABLE contacts (
  id     SERIAL PRIMARY KEY,
  name   VARCHAR(100),
  phones TEXT[]
);

Inserting array data

-- Using the ARRAY constructor
INSERT INTO contacts (name, phones)
VALUES ('John Doe', ARRAY['(408)-589-5846', '(408)-589-5555']);

-- Using curly brace literal syntax
INSERT INTO contacts (name, phones) VALUES
  ('Lily Bush',    '{"(408)-589-5841"}'),
  ('William Gate', '{"(408)-589-5842","(408)-589-58423"}');

Accessing array elements

PostgreSQL arrays use 1-based indexing — the first element is at index 1:

-- Get the first phone number for each contact
SELECT name, phones[1] FROM contacts;

-- Output:
-- name         | phones
-- -------------+----------------
-- John Doe     | (408)-589-5846
-- Lily Bush    | (408)-589-5841
-- William Gate | (408)-589-5842

-- Filter by a specific array element
SELECT name FROM contacts WHERE phones[2] = '(408)-589-58423';

Updating array data

-- Update a single element
UPDATE contacts
SET phones[2] = '(408)-589-5843'
WHERE id = 3
RETURNING *;

-- Replace the entire array
UPDATE contacts
SET phones = '{"(408)-589-5843"}'
WHERE id = 3
RETURNING *;

Searching arrays with ANY()

-- Find contacts who have a specific number anywhere in the array
SELECT name, phones
FROM contacts
WHERE '(408)-589-5555' = ANY(phones);

-- Output:
-- name     | phones
-- ---------+---------------------------------
-- John Doe | {(408)-589-5846,(408)-589-5555}

Expanding arrays to rows with unnest()

SELECT name, unnest(phones)
FROM contacts;

-- Output:
-- name         | unnest
-- -------------+----------------
-- John Doe     | (408)-589-5846
-- John Doe     | (408)-589-5555
-- Lily Bush    | (408)-589-5841
-- William Gate | (408)-589-5843

Production tips

Array subscripts are 1-based in PostgreSQL — the first element is arr[1], not arr[0].
For large tables that need to search inside arrays, create a GIN index: CREATE INDEX idx_phones ON contacts USING GIN (phones); then use @> for containment searches.
Avoid deeply nesting arrays or storing very large arrays in a single column — consider a child table with a foreign key for unbounded one-to-many relationships.
Use unnest() with WITH ORDINALITY when you need the position of each element alongside the value.

Reference: PostgreSQL documentation — Arrays.

Frequently Asked Questions

What is a PostgreSQL array?

A PostgreSQL array is an ordered collection of values all sharing the same data type, stored in a single column. Every data type in PostgreSQL has a corresponding array type: integer[], text[], boolean[], etc. Arrays can be one-dimensional, multidimensional, or even nested. Array subscripts in PostgreSQL are 1-based by default — the first element is at index 1.

How do you define an array column in PostgreSQL?

Add [] after the data type: phones TEXT[] or scores INTEGER[]. For a two-dimensional array use [][]: matrix INTEGER[][]. The dimension declaration is advisory in PostgreSQL — you can insert arrays of any dimension into a column declared as one-dimensional, though this is not recommended.

How do you insert array data in PostgreSQL?

Use the ARRAY constructor: INSERT INTO contacts (phones) VALUES (ARRAY['(408)-589-5846','(408)-589-5555']); Or use the curly brace literal syntax: INSERT INTO contacts (phones) VALUES ('{"(408)-589-5841"}'); In the curly brace syntax, use single quotes around the whole value and double quotes around each text element.

How do you search inside an array in PostgreSQL?

Use the ANY() function to check if a value exists anywhere in the array regardless of position: WHERE '(408)-589-5555' = ANY(phones); For checking whether an array contains all elements of another array, use the @> operator: WHERE phones @> ARRAY['(408)-589-5555']; For a GIN-indexed containment search on large tables, @> is more efficient.

How do you expand a PostgreSQL array into rows?

Use unnest(): SELECT name, unnest(phones) FROM contacts; This returns one row per array element. You can combine unnest() with WITH ORDINALITY to also get the position of each element: SELECT name, phone, pos FROM contacts, unnest(phones) WITH ORDINALITY AS t(phone, pos);