Arrays in PostgreSQL

Postgres supports array columns—ordered lists of a base type. Useful for tags and small multivalues; large or query-heavy many-to-many data usually belongs in junction tables.

Declaring and inserting

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

INSERT INTO articles (title, tags)
VALUES ('Postgres arrays', ARRAY['sql', 'postgres', 'arrays']);

Practice: Run in psql or DB Fiddle (PostgreSQL). Use your practice database from the workflow lesson.

Querying arrays

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

SELECT title, array_length(tags, 1) AS tag_count
FROM articles;

When not to use arrays

Normalized tag tables scale better for analytics and FK integrity. Django ArrayField maps here—migrations require Postgres backend.

Important interview questions and answers

1. Q: ANY vs @> operator?
   A: = ANY checks element membership; @> checks array containment.
2. Q: Array indexing?
   A: Postgres arrays are 1-based in subscripts—unlike Python lists.

Self-check

1. How do you test if tag 'sql' is in tags?
2. When should you prefer a junction table?

Pitfall: Large tag sets in arrays are hard to index—normalize when tags drive analytics.