Menu

Массивы в PostgreSQL: ANY, содержит (@>) и unnest

Столбцы-массивы в PostgreSQL: как их объявлять, индексация с 1, проверка, содержит ли массив значение, через ANY, @> и &&, unnest и нужные функции для массивов.

На этой странице есть исполняемые редакторы: меняйте, запускайте и сразу видите результат.

Массивом может быть любой тип 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.

Иллюстрация языков программирования Coddy

Учитесь программировать с Coddy

Начать