46. Arrays: multiple values in one column

When an array column is the right call

Module 8's 1NF discussion said "no comma-separated lists crammed into one column" — and that's still correct for relational data (a list of foreign keys belongs in a bridge table, not an array). Postgres's native array type is for a genuinely different case: a small, unordered-ish, self-contained list of values that doesn't need its own identity or relationships — pagila's own film.special_features (seen in Module 10's GIN lesson) is exactly this: a handful of tags belonging entirely to one film, never queried as their own entity, never needing a foreign key to anything.

Array literals and basic access

SELECT special_features FROM film WHERE film_id = 1;
-- {"Deleted Scenes","Behind the Scenes"}

-- 1-indexed (not 0-indexed!) — a genuine SQL-standard quirk worth
-- committing to memory deliberately, since it contradicts most
-- programming languages.
SELECT special_features[1] FROM film WHERE film_id = 1;   -- "Deleted Scenes"

-- Array length.
SELECT array_length(special_features, 1) FROM film WHERE film_id = 1;

Array operators

-- @> containment — "does this array contain these values" (you've
-- already used this in Module 10's GIN lesson).
SELECT title FROM film WHERE special_features @> ARRAY['Trailers'];

-- && overlap — "do these two arrays share at least one element."
SELECT title FROM film WHERE special_features && ARRAY['Trailers', 'Commentaries'];

-- || concatenation — append/combine arrays.
SELECT special_features || ARRAY['Extended Edition'] FROM film WHERE film_id = 1;

-- = exact equality (same elements, same order).
SELECT title FROM film WHERE special_features = ARRAY['Trailers', 'Deleted Scenes'];

unnest: turning an array into rows

This is the single most important array function to know well — it inverts the array, producing one output row per array element, directly bridging array data back into ordinary relational, set-based querying:

SELECT film_id, title, unnest(special_features) AS feature
FROM film
WHERE film_id IN (1, 2)
ORDER BY film_id;

unnest is what lets you GROUP BY/aggregate/join on individual array elements — anything this course's relational lessons (Modules 3-6) taught, now applicable to data that's stored as an array:

-- Count how many films have each special feature -- a GROUP BY over
-- values that live INSIDE an array column, made possible by unnest
-- turning them into ordinary rows first.
SELECT unnest(special_features) AS feature, count(*) AS film_count
FROM film
GROUP BY feature
ORDER BY film_count DESC;

array_agg: the inverse of unnest

array_agg is an aggregate function (Module 5) that collects values back into an array, one array per group — the reverse operation:

-- Every category name each film belongs to, collected into one array
-- per film (pagila's film_category is a normalized bridge table --
-- this reconstructs an array VIEW of that relational data on demand).
SELECT f.title, array_agg(c.name ORDER BY c.name) AS categories
FROM film f
JOIN film_category fc ON fc.film_id = f.film_id
JOIN category c ON c.category_id = fc.category_id
GROUP BY f.title
ORDER BY f.title
LIMIT 5;

This is a genuinely useful pattern: keep the data normalized (Module 8) in real bridge tables for integrity and flexible querying, and use array_agg to present it as a convenient array at read time, only when a specific query wants that shape — you get normalization's correctness guarantees and array-shaped output, without paying for either tradeoff permanently.

Check yourself

1. Is a Postgres array 0-indexed or 1-indexed? What's special_features[1] if the array has two elements?
2. What does unnest do to an array column, and why does it let you GROUP BY individual array elements?
3. array_agg and unnest are inverses of each other — in your own words, what does each direction accomplish?