Array Types

Most relational databases require you to break every multi-valued attribute out into a separate related table. PostgreSQL offers a genuinely distinctive alternative: any column can be declared as an array of a base type, letting a single row hold a list of values directly.

Declaring and populating array columns

Array columns

SQL

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name       TEXT NOT NULL,
    tags       TEXT[],
    ratings    INTEGER[]
);

INSERT INTO products (name, tags, ratings) VALUES
    ('Wireless Mouse', ARRAY['electronics', 'accessories'], ARRAY[5, 4, 5, 3]),
    ('Desk Lamp', '{"home", "lighting"}', '{4, 4, 5}');

Both ARRAY[1, 2, 3] syntax and the curly-brace literal form {1, 2, 3} (as a string) create the same array value. The curly-brace form is what you'll see when PostgreSQL prints an array back to you.

Querying arrays

Reading array values and elements

SQL

SELECT name, tags FROM products;

-- Access a single element
SELECT name, tags[1] AS first_tag FROM products;

-- Number of elements
SELECT name, array_length(ratings, 1) AS rating_count FROM products;

name            |            first_tag
----------------+--------------------
Wireless Mouse  | electronics
Desk Lamp       | home

Warning

PostgreSQL array indexes are **1-based**, not 0-based. `tags[1]` gets the first element, and `tags[0]` returns `NULL` rather than raising an error or returning the last element. This is a genuine gotcha for anyone coming from C, Java, JavaScript, Python, or almost any general-purpose programming language, where array/list indexing starts at 0.

Searching inside arrays

PostgreSQL provides dedicated operators for testing membership, containment, and overlap between arrays, on top of the standard ANY / ALL constructs.

ANY, ALL, and array operators

SQL

-- ANY: does the array contain this value?
SELECT name FROM products WHERE 'lighting' = ANY(tags);

-- @>: does the left array contain every element of the right array?
SELECT name FROM products WHERE tags @> ARRAY['electronics'];

-- &&: do the two arrays share at least one element (overlap)?
SELECT name FROM products WHERE tags && ARRAY['lighting', 'furniture'];

-- ALL: is every element of the array greater than 3?
SELECT name FROM products WHERE 3 < ALL(ratings);

Operator / construct	Meaning
`= ANY(array)`	True if the value equals at least one element
`<op> ALL(array)`	True if the comparison holds against every element
`@>`	Contains — the left array includes every element of the right array
`&&`	Overlap — the two arrays share at least one element

Note

Arrays are a good fit for small, denormalized lists of scalar values that belong to exactly one row and don't need their own attributes or relationships — tags, a handful of phone numbers, a fixed set of category labels. Once list items need to be queried, joined, or updated independently, need their own attributes (like a timestamp per item), or can grow unbounded, a proper related table with a foreign key is almost always the better design — that is exactly the normalization trade-off the **Normalization** page covers in more depth.

Any type can become an array type by appending [], e.g. TEXT[], INTEGER[].
Array literals can be written as ARRAY[1, 2, 3] or as the string form '{1,2,3}'.
Array indexing is 1-based — arr[1] is the first element, and arr[0] is NULL.
ANY / ALL test individual elements; @> tests containment and && tests overlap between two arrays.
Use arrays for small, self-contained lists of scalars; use a related table when items need their own attributes or independent querying.