Array Operations

-- Create table with array column
CREATE TABLE posts (
  id    serial PRIMARY KEY,
  title text NOT NULL,
  tags  text[] NOT NULL DEFAULT '{}'
);

-- Insert and query
INSERT INTO posts(title, tags) VALUES ('Hello', ARRAY['postgres','db']);
INSERT INTO posts(title, tags) VALUES ('World', '{"news","db"}');

-- Containment, overlap, and indexing
SELECT * FROM posts WHERE tags @> ARRAY['db'];
SELECT * FROM posts WHERE tags && ARRAY['news'];
SELECT unnest(tags) AS tag FROM posts WHERE id = 1;

-- Update and append
UPDATE posts SET tags = array_append(tags, 'tutorial') WHERE id = 1;

-- GIN index for fast array membership
CREATE INDEX idx_posts_tags ON posts USING gin(tags);