Array Columns (PostgreSQL)

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

INSERT INTO posts (title, tags) VALUES
  ('Intro to Rust',   ARRAY['rust', 'systems', 'tutorial']),
  ('Why Static Sites', ARRAY['web', 'jamstack']);

-- Operators
SELECT * FROM posts WHERE 'rust' = ANY (tags);           -- single element
SELECT * FROM posts WHERE tags @> ARRAY['rust'];          -- contains all
SELECT * FROM posts WHERE tags && ARRAY['rust', 'web'];   -- any overlap
SELECT * FROM posts WHERE array_length(tags, 1) > 2;

-- Unnest — turn an array column into rows (one per element)
SELECT id, title, tag
FROM   posts, unnest(tags) AS tag
WHERE  tag LIKE 'r%';

-- Index for containment lookups
CREATE INDEX idx_posts_tags ON posts USING gin (tags);

-- Array literals + functions
SELECT ARRAY[1,2,3] || ARRAY[4,5];           -- {1,2,3,4,5}
SELECT array_remove(ARRAY[1,2,3,2], 2);      -- {1,3}
SELECT array_agg(DISTINCT category) FROM products;