Array Data Type in PostgreSQL

PostgreSQL lets any data type become an array by appending [] to it — a native alternative to creating a whole separate table just to store a list of values.

Defining an Array Column

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  tags TEXT[]
);

Inserting Array Values

INSERT INTO articles (title, tags)
VALUES ('Intro to Postgres', ARRAY['database', 'sql', 'tutorial']);

-- or with curly-brace literal syntax:
INSERT INTO articles (title, tags)
VALUES ('Intro to Postgres', '{database, sql, tutorial}');

Reading Array Elements

Array indexes in PostgreSQL are 1-based, not 0-based.

SELECT tags[1] FROM articles;  -- the FIRST tag

Querying with ANY

SELECT * FROM articles WHERE 'sql' = ANY(tags);
-- articles where 'sql' is one of the tags

Checking Full Overlap/Containment

SELECT * FROM articles WHERE tags @> ARRAY['sql', 'tutorial'];
-- articles containing BOTH 'sql' AND 'tutorial'

SELECT * FROM articles WHERE tags && ARRAY['sql', 'nosql'];
-- articles that share AT LEAST ONE tag with this list

Updating an Array

UPDATE articles SET tags = array_append(tags, 'postgres')
WHERE id = 1;

UPDATE articles SET tags = array_remove(tags, 'draft')
WHERE id = 1;

unnest() — Turn an Array Into Rows

The array equivalent of MongoDB's $unwind — expands each array element into its own output row.

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

       title        |   tag
---------------------+----------
 Intro to Postgres   | database
 Intro to Postgres   | sql
 Intro to Postgres   | tutorial

This is especially useful combined with GROUP BY to count how often each tag appears across all articles.

Common Errors & Troubleshooting

• tags[0] always returning NULL — PostgreSQL array indexes start at 1, not 0 — this is a classic trip-up for anyone coming from most programming languages.
• = ANY(tags) failing with a type error — the value being compared and the array's element type must match; a bare string literal compared against a TEXT[] array is usually fine, but comparing against an INTEGER[] needs a numeric literal, not a quoted one.
• @> and && mixed up — @> checks "contains ALL of these values"; && checks "shares AT LEAST ONE value" — using the wrong one silently returns a different (often broader or narrower) result set than intended.

Key Takeaways

• Array indexes start at 1, not 0 — a common trip-up coming from most programming languages.
• @> checks "contains all of these", && checks "shares at least one with these".
• unnest() flattens an array column into one row per element — pair it with GROUP BY for tag/category counting.