Arrays

PostgreSQL supports arrays as a native data type — any column can store multiple values of the same type. Arrays are useful for tags, labels, multi-value attributes, and denormalized lookup data. They come with rich operators, functions, and indexing support.

Declaring Array Columns

-- Syntax: typename[] for one-dimensional arrays
CREATE TABLE posts (
    id          SERIAL PRIMARY KEY,
    title       TEXT,
    tags        TEXT[],         -- array of text
    scores      INTEGER[],      -- array of integers
    prices      NUMERIC(10,2)[],-- array of numerics
    matrix      INTEGER[][]     -- two-dimensional array
);

Inserting Arrays

-- Using ARRAY[] constructor
INSERT INTO posts (title, tags, scores) VALUES
    ('Intro to SQL',  ARRAY['sql', 'beginner', 'database'], ARRAY[95, 87, 92]),
    ('PostgreSQL Tips', ARRAY['postgresql', 'advanced'],    ARRAY[98, 94]);

-- Using literal syntax with curly braces
INSERT INTO posts (title, tags) VALUES
    ('JSON in Postgres', '{postgresql, json, jsonb}');

-- Mixed approach (both are valid)
INSERT INTO posts (title, tags, scores) VALUES
    ('Window Functions', ARRAY['sql','analytics'], '{88,90,95}');

Accessing Elements

Arrays in PostgreSQL are 1-based (the first element is at index 1, not 0):

-- Get the first element
SELECT tags[1] FROM posts WHERE id = 1;
-- Result: sql

-- Get the second element
SELECT tags[2] FROM posts;

-- Negative indexes are NOT supported (unlike Python)
-- Use ARRAY_LENGTH to find the last element
SELECT tags[ARRAY_LENGTH(tags, 1)] FROM posts WHERE id = 1;
-- Result: database (last tag)

Slicing Arrays

-- Get elements 1 through 2 (inclusive)
SELECT tags[1:2] FROM posts WHERE id = 1;
-- Result: {sql,beginner}

-- Get from index 2 to the end
SELECT tags[2:] FROM posts WHERE id = 1;
-- Result: {beginner,database}

Array Functions

-- Length of an array (dimension 1)
SELECT ARRAY_LENGTH(tags, 1) AS tag_count FROM posts;

-- Number of dimensions
SELECT ARRAY_NDIMS(matrix) FROM posts;

-- Array lower and upper bounds
SELECT ARRAY_LOWER(tags, 1), ARRAY_UPPER(tags, 1) FROM posts;
-- Returns: 1, 3 (for a 3-element array)

-- Append an element
SELECT ARRAY_APPEND(tags, 'new-tag') FROM posts WHERE id = 1;
-- {sql,beginner,database,new-tag}

-- Prepend an element
SELECT ARRAY_PREPEND('first', tags) FROM posts WHERE id = 1;
-- {first,sql,beginner,database}

-- Concatenate arrays
SELECT ARRAY_CAT(ARRAY[1,2], ARRAY[3,4]);
-- {1,2,3,4}

-- Also: || operator for concatenation
SELECT ARRAY[1,2] || ARRAY[3,4];
-- {1,2,3,4}

-- Remove all occurrences of a value
SELECT ARRAY_REMOVE(ARRAY[1,2,3,2,1], 2);
-- {1,3,1}

-- Replace all occurrences
SELECT ARRAY_REPLACE(ARRAY['a','b','a'], 'a', 'z');
-- {z,b,z}

-- Position of an element (returns NULL if not found)
SELECT ARRAY_POSITION(ARRAY['sql','beginner','database'], 'beginner');
-- 2

-- Positions of all occurrences
SELECT ARRAY_POSITIONS(ARRAY[1,2,1,3,1], 1);
-- {1,3,5}

UNNEST — Expand Array to Rows

UNNEST converts an array into a set of rows — extremely useful for joining against arrays:

-- Expand tags to one row per tag
SELECT id, UNNEST(tags) AS tag FROM posts;

-- Expand with index position (PostgreSQL 9.4+)
SELECT id, idx, tag
FROM posts, UNNEST(tags) WITH ORDINALITY AS t(tag, idx);

-- Unnest multiple arrays in parallel (short-zip — stops at shortest)
SELECT UNNEST(ARRAY['a','b','c']), UNNEST(ARRAY[1,2,3]);
-- a,1  b,2  c,3

-- Convert rows back to array
SELECT ARRAY_AGG(tag ORDER BY tag) AS sorted_tags FROM UNNEST(ARRAY['sql','beginner','database']) AS t(tag);
-- {beginner,database,sql}

Array Operators

-- Posts tagged with 'sql' (containment check)
SELECT title FROM posts WHERE tags @> ARRAY['sql'];

-- Posts tagged with BOTH 'sql' AND 'advanced'
SELECT title FROM posts WHERE tags @> ARRAY['sql', 'advanced'];

-- Posts tagged with 'sql' OR 'postgresql' (overlap)
SELECT title FROM posts WHERE tags && ARRAY['sql', 'postgresql'];

-- Posts where tags is exactly ['sql', 'beginner']
SELECT title FROM posts WHERE tags = ARRAY['sql', 'beginner'];

ANY and ALL with Arrays

-- Check if a value exists in an array (like IN)
SELECT title FROM posts WHERE 'sql' = ANY(tags);
-- Same as: WHERE tags @> ARRAY['sql']

-- Check if a value is NOT in an array
SELECT title FROM posts WHERE 'python' <> ALL(tags);

-- ANY with comparison operators
SELECT * FROM posts WHERE 95 = ANY(scores);
SELECT * FROM posts WHERE 90 <= ALL(scores);  -- all scores >= 90

GIN Index for Array Containment

-- GIN index enables fast @>, &&, and <@ queries
CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

-- These queries now use the index:
SELECT * FROM posts WHERE tags @> ARRAY['sql'];         -- fast!
SELECT * FROM posts WHERE tags && ARRAY['sql','python']; -- fast!

-- This does NOT use the GIN index (equality check on whole array):
SELECT * FROM posts WHERE tags = ARRAY['sql','beginner']; -- Seq Scan
-- Use a B-tree for exact equality queries on arrays

Multi-Dimensional Arrays

-- 2D array
SELECT ARRAY[[1,2,3],[4,5,6]] AS matrix;

-- Access element at row 1, column 2
SELECT (ARRAY[[1,2,3],[4,5,6]])[1][2];
-- Result: 2

-- Dimensions
SELECT ARRAY_NDIMS(ARRAY[[1,2],[3,4]]);  -- 2
SELECT ARRAY_LENGTH(ARRAY[[1,2],[3,4]], 1);  -- 2 (rows)
SELECT ARRAY_LENGTH(ARRAY[[1,2],[3,4]], 2);  -- 2 (columns)

Practical Patterns

Tag System

-- Store tags as an array, GIN index for fast search
CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[]
);
CREATE INDEX articles_tags_gin ON articles USING GIN (tags);

-- Find articles with a specific tag
SELECT title FROM articles WHERE tags @> ARRAY['postgresql'];

-- Find top 10 most used tags across all articles
SELECT tag, COUNT(*) AS usage_count
FROM articles, UNNEST(tags) AS t(tag)
GROUP BY tag
ORDER BY usage_count DESC
LIMIT 10;

Storing Ordered Lists

-- Recent search history per user (ordered, max 10 items)
CREATE TABLE user_searches (
    user_id      INTEGER PRIMARY KEY,
    recent_terms TEXT[]
);

-- Add a search term to the front, keep last 10
UPDATE user_searches
SET recent_terms = (
    ARRAY[:'new_term'] ||
    (SELECT ARRAY(SELECT UNNEST(recent_terms) LIMIT 9))
)
WHERE user_id = :user_id;