Массивы в PostgreSQL

В прошлом уроке мы искали теги внутри записки JSONB. Но если от записки нужен только список тегов, JSONB избыточен. В PostgreSQL у любого типа есть брат-массив: text[], integer[], date[]. Разберём, как с ним работать, на учебной базе магазина, и когда массив лучше связующей таблицы, а когда хуже.

Бусы на нитке

Ячейка таблицы обычно держит одно значение, как коробочка одну бусину. Массив это нитка с бусами: одна ячейка, а на ней несколько значений, и у каждого своё место по порядку.

Массив в PostgreSQL хранит упорядоченный список значений одного типа. Тип записывается как тип элемента с квадратными скобками. Заведём книгам колонку тегов:

ALTER TABLE books ADD COLUMN IF NOT EXISTS tags text[];

UPDATE books SET tags = '{классика,мистика}' WHERE id = 1;
UPDATE books SET tags = '{}' WHERE id = 2;
UPDATE books SET tags = ARRAY['фантастика', 'классика'] WHERE id = 3;
UPDATE books SET tags = ARRAY['фантастика'] WHERE id = 4;
UPDATE books SET tags = ARRAY['алгоритмы', 'python'] WHERE id = 7;
UPDATE books SET tags = ARRAY['программирование', 'классика'] WHERE id = 8;

SELECT id, title, tags FROM books WHERE tags IS NOT NULL ORDER BY id;

 id |       title        |            tags
----+--------------------+-----------------------------
  1 | Мастер и Маргарита | {классика,мистика}
  2 | Собачье сердце     | {}
  3 | Пикник на обочине  | {фантастика,классика}
  4 | Трудно быть богом  | {фантастика}
  7 | Грокаем алгоритмы  | {алгоритмы,python}
  8 | Совершенный код    | {программирование,классика}
(6 rows)

Массив можно записать двумя способами:

• Строкой в фигурных скобках: '{классика,мистика}'. Так массивы печатает psql, и так они приходят в виде текста.
• Конструктором ARRAY['фантастика', 'классика']. Внутри обычные значения SQL, с кавычками и выражениями.

У «Собачьего сердца» пустой массив {}: тегов нет, и это известно. У «Шинели» NULL: про теги ничего не сказано. Разница та же, что между пустой строкой и NULL.

В строковой записи свои правила: пробелы по краям элемента PostgreSQL отбрасывает, а элемент с пробелом внутри или с запятой нужно брать в двойные кавычки. psql так и печатает:

SELECT '{"научная фантастика", классика}'::text[] AS tags;

              tags
---------------------------------
 {"научная фантастика",классика}
(1 row)

Пробел перед «классикой» пропал. Конструктор ARRAY[...] избавляет от этих правил, поэтому в коде надёжнее он.

Номера с единицы

Элементы массива нумеруются с единицы, а не с нуля, как в JavaScript и Python. Номер за пределами массива не ошибка, а NULL:

SELECT title, tags[1] AS first, tags[2] AS second, tags[5] AS fifth
FROM books
WHERE id IN (1, 4)
ORDER BY id;

       title        |   first    | second  | fifth
--------------------+------------+---------+-------
 Мастер и Маргарита | классика   | мистика |
 Трудно быть богом  | фантастика |         |
(2 rows)

tags[0] тоже вернёт NULL, молча. Если привык считать с нуля, ошибку не увидишь, а первый элемент потеряешь.

Длину массива спрашивают двумя функциями, и они по-разному видят пустой массив:

SELECT title, cardinality(tags) AS n, array_length(tags, 1) AS len
FROM books
WHERE id IN (1, 2, 5)
ORDER BY id;

       title        | n | len
--------------------+---+-----
 Мастер и Маргарита | 2 |   2
 Собачье сердце     | 0 |
 Шинель             |   |
(3 rows)

cardinality честно говорит 0 для пустого массива. array_length возвращает NULL, потому что у пустого массива нет даже первого измерения. Бери cardinality.

Поиск по массиву

Четыре вопроса, которые задают массиву чаще всего:

Вопрос	Как спросить
есть ли элемент	'классика' = ANY (tags)
есть ли все эти элементы	tags @> ARRAY['классика']
все ли элементы из списка	tags <@ ARRAY['фантастика', 'классика']
есть ли хоть один общий	tags && ARRAY['мистика', 'python']

ANY читается как «равно хоть одному элементу». Книги с тегом «классика»:

SELECT title FROM books WHERE 'классика' = ANY (tags) ORDER BY id;

       title
--------------------
 Мастер и Маргарита
 Пикник на обочине
 Совершенный код
(3 rows)

Операторы @>, <@ и && сравнивают массив с массивом. Порядок элементов и повторы им безразличны:

SELECT title,
       tags @> ARRAY['классика']               AS has_classic,
       tags <@ ARRAY['фантастика', 'классика'] AS only_these,
       tags && ARRAY['мистика', 'python']      AS any_of
FROM books
WHERE tags IS NOT NULL
ORDER BY id;

       title        | has_classic | only_these | any_of
--------------------+-------------+------------+--------
 Мастер и Маргарита | t           | f          | t
 Собачье сердце     | f           | t          | f
 Пикник на обочине  | t           | t          | f
 Трудно быть богом  | f           | t          | f
 Грокаем алгоритмы  | f           | f          | t
 Совершенный код    | t           | f          | f
(6 rows)

Пустой массив «Собачьего сердца» вложен в любой массив, поэтому <@ для него истинен.

Одна ловушка с отрицанием. Сколько книг без тега «классика»?

SELECT count(*) FROM books WHERE NOT ('классика' = ANY (tags));

 count
-------
     3
(1 row)

Три, а не шесть. Книги с NULL вместо тегов пропали: ANY по NULL даёт NULL, NOT NULL снова NULL, и WHERE такие строки отбрасывает. Это трёхзначная логика в новом месте. Если книги без тегов тоже нужны, допиши OR tags IS NULL.

Список id из приложения

ANY пригождается и без колонки-массива. Бэкенду нужно достать книги по списку id из корзины. Вместо того чтобы склеивать строку IN (1, 3, 7), он передаёт массив одним параметром:

SELECT title, price FROM books WHERE id = ANY ('{1,3,7}'::integer[]) ORDER BY id;

       title        |  price
--------------------+---------
 Мастер и Маргарита |  690.00
 Пикник на обочине  |  450.00
 Грокаем алгоритмы  | 1290.00
(3 rows)

В коде это выглядит как WHERE id = ANY($1), а в $1 драйвер кладёт массив. Запрос один и тот же для списка любой длины.