Arrays

-- 1) Declaring array columns
CREATE TABLE articles (
    id          BIGSERIAL PRIMARY KEY,
    title       TEXT NOT NULL,
    tags        TEXT[]   NOT NULL DEFAULT '{}',
    keyword_ids INTEGER[] NOT NULL DEFAULT '{}',
    body        TEXT,
    published_at TIMESTAMPTZ
);

-- 2) Insert
INSERT INTO articles (title, tags) VALUES
    ('Intro to Postgres', ARRAY['db', 'sql', 'postgres']),
    ('K8s Deep Dive',     '{k8s,cloud,ops}'),                  -- literal syntax
    ('Untagged draft',    DEFAULT);

-- 3) Element access — 1-indexed!
SELECT tags[1] AS first_tag,
             array_length(tags, 1) AS tag_count,
             cardinality(tags)        AS same_thing
FROM articles;

-- 4) Contains operators — fast with GIN index
SELECT * FROM articles WHERE tags @> ARRAY['postgres'];   -- contains 'postgres'
SELECT * FROM articles WHERE tags && ARRAY['k8s','ops'];   -- overlaps (any common element)
SELECT * FROM articles WHERE tags <@ ARRAY['db','sql','postgres','k8s','ops','cloud'];  -- contained by

-- 5) Membership shorthand
SELECT * FROM articles WHERE 'postgres' = ANY(tags);
SELECT * FROM articles WHERE 'draft'    <> ALL(tags);

-- 6) GIN index — the right tool for array filters
CREATE INDEX articles_tags_gin ON articles USING GIN (tags);
-- Now WHERE tags @> ARRAY['x'] is fast even at 100M rows.

-- 7) Append, prepend, remove, concat
UPDATE articles SET tags = array_append(tags, 'newtag') WHERE id = 1;
UPDATE articles SET tags = array_prepend('hot', tags) WHERE id = 1;
UPDATE articles SET tags = array_remove(tags, 'oldtag');
UPDATE articles SET tags = tags || ARRAY['a','b','c'];        -- concat
UPDATE articles SET tags = array_cat(tags, ARRAY['x','y']);    -- same

-- 8) Replace a single element by position
UPDATE articles SET tags[2] = 'renamed' WHERE id = 1;

-- 9) Expanding arrays to rows — UNNEST
SELECT a.id, t.tag
FROM articles a
CROSS JOIN LATERAL unnest(a.tags) AS t(tag);

-- Counting per tag
SELECT t.tag, COUNT(*) AS n
FROM articles a, unnest(a.tags) AS t(tag)
WHERE a.published_at > now() - INTERVAL '30 days'
GROUP BY t.tag
ORDER BY n DESC
LIMIT 20;

-- 10) Aggregating rows back to an array
SELECT array_agg(title ORDER BY published_at DESC) FILTER (WHERE 'postgres' = ANY(tags))
FROM articles;

-- 11) Array of composite type — embedded structures
CREATE TYPE address AS (
    line1   TEXT,
    city    TEXT,
    country TEXT
);

CREATE TABLE customers (
    id         BIGSERIAL PRIMARY KEY,
    addresses  address[]
);

INSERT INTO customers (addresses) VALUES (
    ARRAY[
        ROW('1 Pitt St', 'Sydney', 'AU')::address,
        ROW('200 Park Ave', 'New York', 'US')::address
    ]
);

SELECT (addresses[1]).city FROM customers WHERE id = 1;

-- 12) Slicing
SELECT tags[1:3] FROM articles WHERE id = 1;       -- first three
SELECT tags[2:]   FROM articles WHERE id = 1;       -- from 2 onward

-- 13) Generate sequences for testing
SELECT array(SELECT generate_series(1, 5));        -- {1,2,3,4,5}
SELECT array_fill(0, ARRAY[10]);                    -- ten zeros

-- 14) Sort + dedupe arrays
SELECT array(SELECT unnest(ARRAY[3, 1, 2, 1, 3]) ORDER BY 1);   -- {1,1,2,3,3}
SELECT array(SELECT DISTINCT unnest(ARRAY[3, 1, 2, 1, 3]));     -- {1,2,3}

-- 15) When NOT to use an array
--   • The 'tags' have attributes of their own (color, created_by, slug) → child table
--   • You need referential integrity to a tag table → ARRAY of FK won't enforce it
--   • You need DISTINCT, JOIN, or per-element constraints → child table
--   • Cardinality is unbounded and large (thousands per row) → child table
--
-- Use arrays for:
--   • Sets that semantically belong to the row (e.g. flags, ordered selection)
--   • Read-mostly workloads where contains queries dominate
--   • Vector embeddings (REAL[] / pgvector) for similarity search

-- 16) jsonb vs array
--   array  — same element type, ordered, indexed by GIN, fixed schema
--   jsonb  — heterogeneous, nested, GIN-indexable, flexible schema
-- Use array when the shape is regular; jsonb when callers send variable-shape blobs.

-- 17) Common bugs
--   • 0-indexed thinking — tags[0] is NULL because Postgres arrays are 1-indexed
--   • NULL in an array vs empty array — array_length(arr, 1) is NULL for empty, 0 for {NULL}
--   • IN (array_col) — looking for the WHOLE array as an element; use ANY(array_col)
--   • Forgetting GIN index → seq scans on filters at scale
--   • array_remove returns a NEW array; you must UPDATE with the result
--   • Composite-type arrays without parentheses — (addresses[1]).city, not addresses[1].city

CREATE TABLE posts (id serial primary key, tags text[]);
INSERT INTO posts (tags) VALUES (ARRAY['sql','postgres']);
SELECT * FROM posts WHERE 'sql' = ANY(tags);