Array Columns in PostgreSQL

Unlike most relational databases, PostgreSQL supports native array columns — a single column can hold an ordered list of values of any base type, such as TEXT[], INTEGER[], or even arrays of a composite type. This can be a convenient way to store small, unordered-enough collections (like tags) without introducing a separate join table.

Arrays are powerful but easy to overuse. They work best for simple, denormalized lists that are always read and written together with their parent row, not for data you need to query, join, or update independently.

Why it matters

PostgreSQL can store a list of values in a single column, which is convenient for small sets such as tags or the days a shop is open. Used in the right place it saves a table and a join. Used in the wrong place it brings back the problems that normalization exists to solve.

Declaring and Inserting Arrays

You declare an array column by appending square brackets to a type: tags TEXT[]. Array literals use curly braces in SQL: '{"postgres","sql","database"}', or you can build them with the ARRAY[...] constructor: ARRAY['postgres', 'sql', 'database']. Elements are accessed with 1-based indexing, e.g. tags[1].

Querying with ANY() and unnest()

To check whether a value exists anywhere in an array, use value = ANY(array_column), which is the array equivalent of IN. To turn an array into a set of rows — for example, to JOIN against it, aggregate over it, or filter with normal WHERE clauses per element — use unnest(array_column), which expands each element into its own row.

When Arrays Are (and Aren't) the Right Choice

Arrays are appropriate for small, denormalized, order-sensitive lists that belong wholly to one row, such as a list of tags on a blog post or a set of allowed statuses. They become the wrong choice once you need to query "which rows reference this specific tag" efficiently at scale without an index, enforce referential integrity on the elements (foreign keys cannot target elements inside an array), or frequently add/remove individual elements — a proper join table (post_id, tag_id) is more relationally correct for those cases.

Examples

CREATE TABLE posts (
    id   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title TEXT NOT NULL,
    tags  TEXT[] DEFAULT '{}'
);

INSERT INTO posts (title, tags)
VALUES ('Intro to PostgreSQL', ARRAY['postgres', 'sql', 'database']);

SELECT title FROM posts WHERE 'sql' = ANY(tags);

CREATE TABLE
INSERT 0 1
        title
----------------------
 Intro to PostgreSQL

SELECT title, unnest(tags) AS tag FROM posts;

        title         |   tag
----------------------+----------
 Intro to PostgreSQL  | postgres
 Intro to PostgreSQL  | sql
 Intro to PostgreSQL  | database

Exercise

This exercise uses PostgreSQL arrays, so it needs a PostgreSQL server. Create an articles table with a text array column tags. Insert an article tagged sql and postgres, and another tagged python. Write a query that finds the titles of the articles tagged sql, and a second query that returns one row for each article and tag pair.

Expected result: the first query returns the first article; the second returns three rows.

-- TODO: articles (id, title, tags text array)

-- TODO: insert the two articles

-- TODO: titles of the articles that have the tag 'sql'

-- TODO: one row per article and tag

CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO articles (id, title, tags) VALUES
  (1, 'Indexing basics', ARRAY['sql', 'postgres']),
  (2, 'Decorators', ARRAY['python']);

SELECT title
FROM articles
WHERE 'sql' = ANY(tags);

SELECT title, unnest(tags) AS tag
FROM articles;

Quick quiz

Interview questions