Working with Arrays

CREATE TABLE posts (
   id   bigserial PRIMARY KEY,
   tags text[] NOT NULL DEFAULT '{}',
   scores int[][]      -- multidimensional
);

INSERT INTO posts(tags) VALUES (ARRAY['sql','perf']);
INSERT INTO posts(tags) VALUES ('{"intro","first"}');

SELECT tags[1], tags[array_length(tags,1)] FROM posts;

UPDATE posts SET tags[1] = 'lead'  WHERE id = 1;
UPDATE posts SET tags    = tags || 'extra' WHERE id = 1;
UPDATE posts SET tags    = array_remove(tags, 'old') WHERE id = 1;

SELECT * FROM posts WHERE 'sql' = ANY(tags);
SELECT * FROM posts WHERE tags @> ARRAY['sql','perf'];   -- contains all
SELECT * FROM posts WHERE tags && ARRAY['sql','db'];    -- overlap

SELECT array_append(ARRAY[1,2], 3);    -- {1,2,3}
SELECT array_prepend(0, ARRAY[1,2]);   -- {0,1,2}

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);   -- {1,2,3,4}

SELECT array_remove(ARRAY[1,2,3,2], 2);          -- {1,3}
SELECT array_replace(ARRAY[1,2,3,2], 2, 9);      -- {1,9,3,9}

SELECT id, t.tag FROM posts, unnest(tags) AS t(tag);
SELECT * FROM unnest(ARRAY[10,20,30]) WITH ORDINALITY AS u(val, idx);

SELECT customer_id, array_agg(id ORDER BY placed_at) AS order_ids
  FROM orders GROUP BY customer_id;

SELECT (ARRAY[10,20,30,40,50])[2:4];   -- {20,30,40}
SELECT tags[1:3] FROM posts;

SELECT (ARRAY[[1,2],[3,4]])[2][1];   -- 3
SELECT array_dims(ARRAY[[1,2],[3,4]]); -- [1:2][1:2]

CREATE INDEX posts_tags_gin ON posts USING gin (tags);
EXPLAIN ANALYZE SELECT * FROM posts WHERE tags @> ARRAY['sql'];