Array Operations

Code

-- 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);

Explanation

PostgreSQL arrays let you store multiple values in one column, useful for tags or one-to-many relationships that don't need a join table. Operators like @> (contains) and && (overlap) test array contents, accelerated by a GIN index. unnest expands an array into rows for further processing.

More PostgreSQL Snippets