Массивом может быть любой тип PostgreSQL. Объявите столбец как text[] или int[], записывайте значения как '{a,b}' или ARRAY['a', 'b'] и читайте элементы через tags[1] (индексы начинаются с 1). Чтобы проверить, содержит ли массив значение, используйте 'value' = ANY(tags) или tags @> ARRAY['value'].
У постов 1 и 2 есть тег sql. tags[1] это первый элемент, а cardinality считает их количество. Обе формы литерала хранят одно и то же значение; ARRAY[...] удобнее, когда элементы получаются из выражений или содержат запятые.
Проверить, содержит ли массив значение
Проверку вхождения покрывают четыре оператора:
| Проверка | Смысл | Истина для '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | значение есть в массиве | да |
tags @> '{sql,json}' | массив содержит все эти значения | да |
tags <@ '{sql,json,go}' | каждый элемент есть в этом списке | да |
tags && '{go,json}' | у массивов есть хотя бы один общий элемент | да |
Оба тега есть только у поста 1. Посты 2 и 3 имеют общий элемент с {go,json}. Пост 3 единственный, у которого no_go равно false. ALL это пара к ANY: сравнение должно выполняться для каждого элемента.
= ANY следует правилам SQL для NULL. Если значение не найдено, а в массиве есть NULL, результат будет NULL, а не false. Поэтому WHERE NOT ('x' = ANY(tags)) может отбросить строки, которые вы ожидали увидеть. @> и && возвращают true или false.
GIN-индекс ускоряет @>, <@, && и = на больших таблицах. Для = ANY(column) он не помогает, поэтому для поиска по индексу пишите tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Развернуть массив в строки через unnest
unnest возвращает по одной строке на элемент, так что можно считать, соединять или фильтровать отдельные элементы:
sql встречается в двух постах, остальные теги по одному разу. Добавьте WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)), чтобы получить ещё и позицию каждого элемента. Чтобы собрать строки обратно в массив, используйте array_agg (см. string_agg и array_agg).
Добавить, удалить и изменить элементы
Запрос возвращает {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} и a, b, c. В UPDATE те же функции меняют сохранённый массив: SET tags = array_append(tags, 'new') или SET tags = array_remove(tags, 'old'). array_remove удаляет все копии значения. string_to_array('a,b,c', ',') разбивает текст на массив.
Узнать длину массива
cardinality(tags) возвращает число элементов, а для пустого массива 0. array_length(tags, 1) возвращает длину первого измерения, а для пустого массива NULL. Поэтому WHERE array_length(tags, 1) = 0 не срабатывает никогда. Проверяйте пустой массив через cardinality(tags) = 0 или tags = '{}'.
Массивы в PostgreSQL бывают многомерными (int[][]), но все вложенные массивы должны быть одной длины, а объявленный размер (int[3]) не проверяется. Для вложенных или неровных данных обычно лучше подходит JSONB.
Часто задаваемые вопросы
Массивы в PostgreSQL нумеруются с 0 или с 1?
По умолчанию с 1: tags[1] это первый элемент. Индекс за пределами массива возвращает NULL, а не ошибку.
Чем IN отличается от = ANY в PostgreSQL?
x IN (1, 2, 3) принимает список, записанный прямо в запросе, а x = ANY(array) принимает одно значение-массив. ANY удобнее в коде приложения: можно передать один параметр-массив (WHERE id = ANY($1)) вместо того, чтобы собирать список плейсхолдеров.
Что лучше: столбец-массив или отдельная таблица?
Массив подходит для короткого списка, который читается и записывается вместе со своей строкой, например для тегов. Если элементам нужны собственные столбцы, внешние ключи или они обновляются по одному, отдельную таблицу с одной строкой на элемент проще запрашивать и ограничивать.
Как проверить, что массив пустой, в PostgreSQL?
Используйте cardinality(arr) = 0 или arr = '{}'. Условие array_length(arr, 1) = 0 не срабатывает никогда, потому что для пустого массива array_length возвращает NULL.