Arrays in PostgreSQL

~12 min · extensions, arrays

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

First-class array columns

PostgreSQL has native array types — TEXT[], INTEGER[], even JSONB[] if you must. Arrays are great for short, rarely-modified lists where the order matters and you query by membership.

The array operators

= exact array equality.
@> contains: tags @> ARRAY['red','wool'].
<@ contained by: ARRAY['red'] <@ tags.
&& any element in common: tags && ARRAY['red','blue'].
unnest() turns an array into rows for joining/aggregating.

Arrays vs junction tables

Arrays save a join table when the list is small and conceptually part of the row. They become awkward when the items have their own attributes (then you want a junction table) or when the list grows large (rewriting an array on every insert is O(n)).

Code

Array column basics·sql

CREATE TABLE products (
    id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    tags TEXT[] NOT NULL DEFAULT '{}'
);

INSERT INTO products (name, tags) VALUES
    ('Wool Sweater', ARRAY['red','wool','winter']),
    ('Cotton Tee',   ARRAY['blue','cotton','summer']);

-- Find products tagged 'red'
SELECT * FROM products WHERE 'red' = ANY(tags);
SELECT * FROM products WHERE tags @> ARRAY['red'];

-- Add a tag (immutable: produce a new array)
UPDATE products SET tags = array_append(tags, 'sale') WHERE id = 1;

Index for fast membership queries·sql

CREATE INDEX products_tags_gin ON products USING gin (tags);

EXPLAIN ANALYZE
SELECT * FROM products WHERE tags @> ARRAY['wool'];
-- Bitmap Index Scan on products_tags_gin

Unnest for aggregation·sql

-- Top tags across the catalogue
SELECT tag, COUNT(*) AS uses
FROM   products, UNNEST(tags) AS tag
GROUP  BY tag
ORDER  BY uses DESC
LIMIT  10;

External links

PostgreSQL Arrays

Exercise

Add a TEXT[] tags column to a table. Insert rows; query with @> and &&. Add a GIN index; compare EXPLAIN. Then UNNEST to get tag-frequency counts across all rows.