PostgreSQL supports array columns — a single column can hold multiple values of the same type. This is a distinctive Postgres feature useful for storing lists (tags, roles, scores) without a separate table, with operators and functions for querying.
posts (
id SERIAL ,
title TEXT,
tags TEXT[]
);
posts (title, tags)
(, [, ]),
(, );
The type[] syntax declares an array column. You insert with ARRAY[...] or '{...}' literal syntax.
-- ANY — does the array contain a value?
SELECT * FROM posts WHERE 'ruby' = ANY(tags);
-- @> containment — does the array contain ALL of these?
SELECT * FROM posts WHERE tags @> ARRAY['ruby', 'web'];
-- && overlap — do the arrays share ANY element?
SELECT * FROM posts WHERE tags && ARRAY['tech', 'news'];
-- access by index (1-based!) and length
SELECT tags[1], array_length(tags, 1) FROM posts;
-- unnest — expand an array into rows
SELECT id, unnest(tags) AS tag FROM posts; -- one row per tag
Operators like ANY (contains a value), @> (containment), and && (overlap), plus functions like unnest (expand to rows) and array_agg (aggregate rows into an array), make arrays queryable.
-- a GIN index makes array containment/membership queries fast
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- now WHERE tags @> ARRAY['ruby'] uses the index
✓ USE for: simple lists tightly coupled to the row (tags, roles, a few scores)
where you don't need to query/join the individual values much
✗ AVOID when: you need to query, join, or maintain the values relationally,
or the list is large/complex → a separate related table (normalized) is better
→ Arrays are convenient but can violate normalization; use them judiciously.
Array columns are a distinctive and useful PostgreSQL feature, and understanding them is valuable both for leveraging Postgres's capabilities and for making sound design decisions.
Arrays let you store multiple values in a single column (tags, roles, lists) without a separate table — convenient for simple lists tightly coupled to a row.
Understanding how to define, insert, and especially query arrays (operators like ANY for membership, @> for containment, && for overlap, and functions like unnest to expand arrays into rows) is necessary to use them effectively, and knowing that GIN indexes make array queries efficient is important for performance.
Equally important is the design judgment of when to use arrays versus a normalized related table: arrays are convenient for simple lists you don't need to query, join, or maintain relationally, but they can violate normalization principles and become awkward when you need to query individual values, join on them, or manage a complex/large list — in which case a separate related table is the better, normalized choice.
Understanding this trade-off (arrays for convenience with simple coupled lists, separate tables for relationally-managed data) reflects sound design judgment.
Since arrays are a useful, distinctive Postgres feature that can simplify certain designs (avoiding a join table for simple lists) but can be misused (denormalizing data that should be relational), understanding array columns — how to define, query, and index them, and crucially when they're appropriate versus when a normalized table is better — is valuable, practically-relevant knowledge for designing Postgres schemas that use the right approach for each kind of multi-valued data.