PostgreSQL arrays: syntax, operators, and indexing

Quick answer

CREATE TABLE posts (
  id   int PRIMARY KEY,
  tags text[]
);

INSERT INTO posts VALUES (1, ARRAY['sql','postgres']);
INSERT INTO posts VALUES (2, '{docker,sql}');

SELECT * FROM posts WHERE tags @> ARRAY['sql'];

Two literal syntaxes, both valid: ARRAY['a','b'] and '{a,b}'. The ARRAY[] form is clearer and does not need quoting rules memorized.

Declaring array columns

Append [] to any type:

CREATE TABLE t (
  tags       text[],
  scores     int[],
  matrix     int[][],
  fixed      int[3]     -- the 3 is decorative, see below
);

PostgreSQL does not enforce array size or dimensionality. int[3] accepts an array of any length, and int[][] accepts a one-dimensional array. The declared bounds are documentation, not constraints. If you need a limit, use a CHECK:

CREATE TABLE t (
  tags text[] CHECK (cardinality(tags) <= 5)
);

Arrays are 1-indexed

SELECT (ARRAY['a','b','c'])[1] AS first,
       (ARRAY['a','b','c'])[0] AS zero,
       (ARRAY['a','b','c'])[99] AS oob;

 first | zero | oob
-------+------+-----
 a     |      |

Two things there. Indexing starts at 1, so [0] is empty. And an out-of-range index returns NULL rather than raising an error.

That second point deserves care. There is no bounds checking, so a loop that walks off the end of an array gets NULLs instead of a failure, and NULLs propagate quietly through arithmetic. If you are indexing by a computed position, check cardinality first.

Slices use a colon and are inclusive at both ends:

SELECT (ARRAY['a','b','c','d'])[2:3];   -- {b,c}
SELECT (ARRAY['a','b','c','d'])[2:];    -- {b,c,d}

array_length vs cardinality

This is the trap that produces real bugs:

SELECT array_length('{}'::int[], 1) AS empty_len,
       cardinality('{}'::int[])     AS empty_card;

 empty_len | empty_card
-----------+------------
           |          0

array_length on an empty array returns NULL, not 0.

So WHERE array_length(tags, 1) = 0 never matches, and WHERE array_length(tags, 1) > 0 excludes empty arrays and silently behaves oddly in NOT conditions, because NULL is not false.

cardinality() returns 0 for an empty array, which is what you almost always want. Use it.

array_length also needs a dimension argument (1 for a normal array), which is a hint that it was designed for the multidimensional case rather than the common one.

Containment and search operators

These are the operators that make array columns worth having:

SELECT ARRAY[1,2,3] @> ARRAY[2]   AS contains,
       ARRAY[1,2]   && ARRAY[2,3] AS overlaps,
       2 = ANY(ARRAY[1,2,3])      AS any_eq;

 contains | overlaps | any_eq
----------+----------+--------
 t        | t        | t

Practical translations:

-- Posts tagged 'sql'
SELECT * FROM posts WHERE tags @> ARRAY['sql'];

-- Posts tagged both 'sql' AND 'postgres'
SELECT * FROM posts WHERE tags @> ARRAY['sql','postgres'];

-- Posts tagged 'sql' OR 'docker'
SELECT * FROM posts WHERE tags && ARRAY['sql','docker'];

@> with multiple elements is AND. && is OR. That is the single most useful thing to remember here.

ANY vs @>

= ANY(tags) and tags @> ARRAY[x] both test membership, and read almost identically. Only @> can use a GIN index (demonstrated in the indexing section below), so prefer it on any column you have indexed.

ANY is still the right tool when comparing against a subquery or a parameter list:

SELECT * FROM posts WHERE id = ANY(ARRAY[1,2,3]);
SELECT * FROM posts WHERE id = ANY($1);   -- one parameter, a whole list

That last form is genuinely useful: it lets a driver pass a list as a single bind parameter, instead of building IN (?,?,?) dynamically.

unnest and array_agg

unnest expands an array into rows:

SELECT id, unnest(tags) AS tag FROM posts ORDER BY id;

 id |   tag
----+----------
  1 | sql
  1 | postgres
  2 | docker
  2 | sql

Three rows went in and four came out. Note what is missing: post 3, whose tags is an empty array, produced no rows at all.

That is the unnest gotcha. It behaves like an inner join, so rows with empty or NULL arrays disappear from your result. To keep them, use a lateral left join:

SELECT p.id, t.tag
FROM posts p
LEFT JOIN LATERAL unnest(p.tags) AS t(tag) ON true
ORDER BY p.id;

Now post 3 appears with a NULL tag.

array_agg is the inverse, collapsing rows into an array:

SELECT array_agg(tag ORDER BY tag) FROM (SELECT unnest(tags) AS tag FROM posts) x;

array_agg accepts ORDER BY inside the call, which is the only way to guarantee element order. Without it, order is whatever the plan happens to produce.

To deduplicate:

SELECT array_agg(DISTINCT tag ORDER BY tag) FROM ...;

Indexing arrays

A plain B-tree index on an array column is nearly useless: it can only serve whole-array equality. For containment queries you want GIN:

CREATE INDEX posts_tags_gin ON posts USING GIN (tags);

That index accelerates @>, <@, and &&. It is the entire reason array columns are viable at scale.

It does not accelerate = ANY. On a 20,000-row table with a GIN index present:

EXPLAIN (COSTS OFF) SELECT * FROM gin_t WHERE tags @> ARRAY['t7'];

 Bitmap Heap Scan on gin_t
   Recheck Cond: (tags @> '{t7}'::text[])
   ->  Bitmap Index Scan on gin_t_idx
         Index Cond: (tags @> '{t7}'::text[])

EXPLAIN (COSTS OFF) SELECT * FROM gin_t WHERE 't7' = ANY(tags);

 Seq Scan on gin_t
   Filter: ('t7'::text = ANY (tags))

Same question, same index, and the = ANY form scans the whole table. Rewrite membership tests as @> on indexed columns.

GIN indexes are slower to update than B-trees and larger on disk. For a write-heavy table with a rarely-queried array, the index may cost more than it saves.

Arrays of NULL, and NULL arrays

Three different things, and they are not interchangeable:

SELECT NULL::text[]        AS null_array,
       '{}'::text[]        AS empty_array,
       ARRAY[NULL]::text[] AS array_of_null;

• NULL array: the column has no value.
• {}: an array with zero elements.
• {NULL}: an array with one element, which is NULL. cardinality is 1.

Test for them differently:

WHERE tags IS NULL              -- no array
WHERE cardinality(tags) = 0     -- empty array
WHERE tags @> ARRAY[NULL]       -- does NOT work as expected

That last line is worth flagging: containment with NULL does not behave usefully, because NULL is not equal to anything, including itself. Use array_position(tags, NULL) IS NOT NULL to find a NULL element.

A NOT NULL DEFAULT '{}' on array columns avoids most of this class of problem, and is a good default.

Useful functions

When not to use an array

Arrays are tempting and frequently the wrong choice. Use a proper join table when:

• The elements are entities, with their own attributes. Tags with descriptions and colours are a table, not a text[].
• You need foreign keys. PostgreSQL cannot enforce a foreign key on array elements. An int[] of user ids will happily reference users that no longer exist.
• You need to update elements individually and concurrently. Updating one element rewrites the entire array, so two concurrent updates to different elements of the same row conflict.
• The array grows without bound. Rows have a size limit and large arrays get TOASTed, which is slower than a join.

Arrays are a good fit when the elements are simple values, belong wholly to the row, are read together, and are small in number. A list of tags on a post is the canonical good case. A list of order line items is not.

Common problems

array_length returns NULL for my empty arrays. By design. Use cardinality.

Rows disappeared when I used unnest. Empty and NULL arrays produce no rows. Use LEFT JOIN LATERAL ... ON true.

My @> query is slow. No GIN index. Add one.

Out-of-bounds access returns NULL instead of erroring. By design. Check cardinality before indexing by a computed position.

ARRAY['a','b'] = ARRAY['b','a'] is false. Arrays are ordered. For set semantics use @> in both directions, or sort before comparing.

Quick reference