How do you use array columns in PostgreSQL, and when are they a bad idea?
A column can hold an array such as text[], queried with ANY, the contains operator @> and unnest. They suit small, simple lists read together with the row; use a separate table when you need foreign keys, per-item data, or heavy filtering by item.
Explain it simply
An array column is like a small tray attached to a row: a student row with a tray of skill tags. It is handy when the list belongs to the row and is always used with it.
PostgreSQL arrays are 1-indexed, so skills[1] is the first item.
Example
CREATE TABLE candidates (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text,
skills text[]
);
INSERT INTO candidates (name, skills)
VALUES ('Riya', ARRAY['sql','python','git']);
SELECT name FROM candidates WHERE 'sql' = ANY (skills); -- has one value
SELECT name FROM candidates WHERE skills @> ARRAY['sql','git']; -- has all values
SELECT unnest(skills) FROM candidates; -- one row per itemA GIN index on skills speeds up @> and && (overlap) searches.
What to say in the interview
"Arrays are fine for small tag-like lists that travel with the row, and GIN indexes make containment queries fast. If items need foreign keys, their own attributes, or frequent updates to single items, I use a child table, because arrays cannot enforce referential integrity."
Common mistakes
- Storing comma-separated text instead of a real array.
- Forgetting arrays start at 1, not 0.