Skip to content
Tutorials Center
My learning

Topics

Lesson 13 of 90 ยท Data Types

Arrays

Store and query PostgreSQL array columns with literals, ANY and ALL, unnest, array_agg, slices and the @> and && operators, and learn when a join table is better.

Most databases force you to store a list of values in a separate table. PostgreSQL lets a column hold an array: a list of values of one type, such as the tags on a post or the phone numbers of a contact. Arrays can be indexed, searched, expanded into rows and built back up with aggregates, which makes them a handy tool for small, simple lists.

They are also easy to misuse. This lesson shows the native syntax with read-only blocks, because SQLite has no array type. It then practices the same operations live by storing the list as a JSON array, which SQLite does support, and finishes with a clear rule for when a normal join table is the better design.

What arrays are good for

Tags A short list owned by one row.
Membership Does the list contain a value?
Aggregation Collect many rows into one list.

Think "attribute with several values", not "related records".

1 / 2

Declaring array columns and literals

Add [] after any type to make it an array type. Values can be written with the ARRAY[...] constructor or as a quoted literal in curly braces:

SQL
-- Read-only: PostgreSQL
CREATE TABLE posts (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  title text NOT NULL,
  tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO posts (title, tags) VALUES
  ('Intro to indexes', ARRAY['database', 'performance']),
  ('Backups explained', '{database,operations}'),
  ('Typed languages',   ARRAY['languages']);

SELECT ARRAY[1, 2, 3];                  -- {1,2,3}
SELECT '{10,20,30}'::int[];             -- {10,20,30}
SELECT ARRAY[[1,2],[3,4]];              -- {{1,2},{3,4}}  (two dimensions)

Array subscripts start at 1, not 0. Reading beyond the end returns NULL rather than an error:

SQL
-- Read-only: PostgreSQL
SELECT tags[1] FROM posts WHERE title = 'Intro to indexes';   -- database
SELECT tags[5] FROM posts WHERE title = 'Intro to indexes';   -- NULL
SELECT (ARRAY[10,20,30,40,50])[2:4];                          -- {20,30,40}  (slice)
SELECT array_length(tags, 1), cardinality(tags) FROM posts;   -- number of elements

PostgreSQL does not enforce declared dimensions or sizes: int[3] is accepted, but the 3 is ignored.

Searching arrays

You will use four patterns again and again.

Does a value belong to the array? value = ANY(array) is true if any element matches; value = ALL(array) requires every element to match.

SQL
-- Read-only: PostgreSQL
SELECT title FROM posts WHERE 'performance' = ANY (tags);
-- Intro to indexes

The operator form of the same idea uses containment and overlap:

Operator Meaning
a @> b a contains every element of b
a <@ b a is contained by b
a && b the arrays share at least one element
a || b concatenate arrays, or append an element
SQL
-- Read-only: PostgreSQL
SELECT title FROM posts WHERE tags @> ARRAY['database'];                  -- has the tag
SELECT title FROM posts WHERE tags @> ARRAY['database', 'operations'];    -- has both
SELECT title FROM posts WHERE tags && ARRAY['performance', 'languages'];  -- has either

@> and && can use a GIN index, so they stay fast on large tables, whereas = ANY(tags) generally cannot. GIN and GiST Indexes shows how:

SQL
-- Read-only: PostgreSQL
CREATE INDEX posts_tags_gin ON posts USING gin (tags);

Changing arrays

SQL
-- Read-only: PostgreSQL
UPDATE posts SET tags = array_append(tags, 'sql')   WHERE id = 1;
UPDATE posts SET tags = array_remove(tags, 'sql')   WHERE id = 1;
UPDATE posts SET tags = tags || ARRAY['new', 'x']   WHERE id = 2;
SELECT array_position(tags, 'database') FROM posts; -- 1-based position, NULL if absent

Every one of these writes a complete new row version containing the whole array. That is fine for a handful of tags, but a poor fit for a list that is edited all the time by many sessions.

unnest and array_agg: arrays to rows and back

unnest() turns an array into a set of rows, one per element. It is the bridge from array thinking to ordinary relational queries:

SQL
-- Read-only: PostgreSQL
SELECT p.title, t.tag
FROM posts p, unnest(p.tags) AS t(tag);
-- Intro to indexes   | database
-- Intro to indexes   | performance
-- Backups explained  | database
-- Backups explained  | operations
-- Typed languages    | languages

-- How many posts per tag?
SELECT t.tag, count(*) AS posts
FROM posts p, unnest(p.tags) AS t(tag)
GROUP BY t.tag
ORDER BY posts DESC, t.tag;

The reverse, array_agg(), collects the values of a group into one array. Use ORDER BY inside it to control the order:

SQL
-- Read-only: PostgreSQL
SELECT author_id, array_agg(title ORDER BY title) AS titles
FROM articles
GROUP BY author_id;

For text output you can use string_agg(title, ', ' ORDER BY title) instead.

The same ideas live, with JSON arrays

SQLite cannot store a native array, but a JSON array in a text column does the same job, and its JSON functions mirror the PostgreSQL ones. The correspondence is useful for practice:

PostgreSQL array SQLite JSON array
tags[1] (1-based) json_extract(tags, '$[0]') (0-based)
cardinality(tags) json_array_length(tags)
'x' = ANY (tags) EXISTS (SELECT 1 FROM json_each(tags) WHERE value = 'x')
unnest(tags) json_each(tags)
array_agg(x) json_group_array(x)
SQL Searching and unnesting a JSON array

Press Run (or Ctrl + Enter) to see the result here.

The "database" tag appears twice and the other three once each. Overlap and containment come from comparing two sets of elements. Here are @> (has all of the tags) and && (has any of the tags) written in SQLite:

SQL Containment and overlap, spelled out

Press Run (or Ctrl + Enter) to see the result here.

Compare the amount of SQL: PostgreSQL's @> and && operators replace those subqueries with one short expression, and a GIN index can answer them quickly.

When not to use arrays

Arrays are a good fit for a small list that belongs entirely to one row and is read and written as a unit: tags, a list of phone numbers, a set of feature flags. Prefer a separate table with a foreign key when:

  • The elements are entities. If a tag has its own name, color or description, or must be unique across the system, give it a tags table and link it with a join table. An array cannot have a foreign key on its elements.
  • You need integrity. Nothing stops a typo like 'databse' in a text[]. With a tags table the foreign key rejects it.
  • You update elements often. Each change rewrites the whole row, and concurrent updates to the same array conflict.
  • You query across rows by element. "Which posts have tag X" is fast with a GIN index, but "count of posts per tag with metadata and per-tag statistics" is simpler and clearer with a join table.
  • The list can grow without bound. Arrays of thousands of items make rows large and slow.

A join table is the standard relational answer, and you can always rebuild an array view of it with array_agg(). See Table Inheritance and Common Design Patterns for tagging patterns.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does tags[1] return for the array '{database,performance}'?

    PostgreSQL array subscripts start at 1, so tags[1] is the first element. Reading past the end gives NULL, not an error.
  2. 2. Which condition can use a GIN index on a text[] column?

    GIN indexes support the containment and overlap operators @>, <@ and &&. ANY, array_length and subscripts need a different index or a scan.
  3. 3. What does unnest(array) do?

    unnest is a set-returning function that produces a row for each element, so you can join, filter and group the elements like ordinary rows.
  4. 4. When is a join table a better design than an array column?

    Array elements cannot reference other tables or be validated against them. If tags are real entities, a tags table with a join table keeps the data consistent.

Summary

  • Add [] to a type for an array column; build values with ARRAY[...] or '{a,b}'; subscripts start at 1.
  • Search with = ANY(arr), @>, <@ and &&; the last three can use a GIN index.
  • Change arrays with array_append, array_remove and ||; each change rewrites the whole row.
  • unnest() turns an array into rows; array_agg() (or string_agg()) collects rows back into a list.
  • SQLite practiced the same ideas with JSON arrays, json_each and json_group_array, though PostgreSQL's operators are much shorter.
  • Use arrays for short lists owned by one row; use a join table when elements need constraints, metadata or heavy updates.

Next: JSON and JSONB.