Массивы в PostgreSQL: хранение, запросы и функции

PostgreSQL — единственная из популярных реляционных БД с нативной поддержкой массивов. Это мощный инструмент, но с подводными камнями. Разберём всё от синтаксиса до индексации.

Создание таблицы с массивом

CREATE TABLE articles (
    id       SERIAL PRIMARY KEY,
    title    TEXT,
    tags     TEXT[],        -- массив строк
    scores   INTEGER[],     -- массив целых чисел
    matrix   INTEGER[][]    -- двумерный массив
);

Вставка данных

-- Через фигурные скобки (строковый литерал)
INSERT INTO articles (title, tags)
VALUES
    ('SQL для начинающих', '{sql, postgresql, beginner}'),
    ('Оконные функции',    '{sql, window-functions, advanced}'),
    ('Оптимизация',        '{postgresql, indexes, performance}');

-- Через функцию ARRAY[]
INSERT INTO articles (title, tags)
VALUES ('JOIN подробно', ARRAY['sql', 'join', 'basics']);

-- Пустой массив
INSERT INTO articles (title, tags) VALUES ('Без тегов', '{}');
INSERT INTO articles (title, tags) VALUES ('Без тегов', ARRAY[]::TEXT[]);

Чтение элементов

Индексация в PostgreSQL начинается с 1 (не с 0):

SELECT
    title,
    tags,            -- весь массив
    tags[1],         -- первый элемент
    tags[2],         -- второй элемент
    tags[1:2],       -- срез: первые два элемента
    ARRAY_LENGTH(tags, 1) AS tags_count,  -- длина массива (1 = первое измерение)
    CARDINALITY(tags) AS cardinality      -- то же самое для одномерного
FROM articles;

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

-- Содержит ли массив элемент?
SELECT * FROM articles WHERE 'sql' = ANY(tags);

-- Не содержит элемент
SELECT * FROM articles WHERE NOT ('advanced' = ANY(tags));

-- Массив содержит все указанные элементы (@>)
SELECT * FROM articles WHERE tags @> ARRAY['sql', 'join'];

-- Массивы пересекаются (&&)
SELECT * FROM articles WHERE tags && ARRAY['sql', 'python'];

-- Конкретный элемент по позиции
SELECT * FROM articles WHERE tags[1] = 'sql';

Модификация массивов

-- Добавить элемент в конец
UPDATE articles
SET tags = array_append(tags, 'new-tag')
WHERE id = 1;

-- Добавить элемент в начало
UPDATE articles
SET tags = array_prepend('featured', tags)
WHERE id = 1;

-- Объединить два массива
UPDATE articles
SET tags = tags || ARRAY['extra-tag', 'another-tag']
WHERE id = 1;

-- Удалить элемент по значению
UPDATE articles
SET tags = array_remove(tags, 'old-tag')
WHERE id = 1;

-- Заменить элемент
UPDATE articles
SET tags[1] = 'updated-tag'
WHERE id = 1;

UNNEST: развернуть массив в строки

-- Каждый тег как отдельная строка
SELECT id, title, UNNEST(tags) AS tag
FROM articles;
-- Статья с 3 тегами → 3 строки

-- Подсчёт статей по тегу
SELECT tag, COUNT(*) AS article_count
FROM articles, UNNEST(tags) AS tag
GROUP BY tag
ORDER BY article_count DESC;

-- UNNEST с порядковым номером (PostgreSQL 9.4+)
SELECT id, elem, pos
FROM articles
CROSS JOIN LATERAL UNNEST(tags) WITH ORDINALITY AS u(elem, pos);
-- Возвращает: id | tag | позиция_тега (1, 2, 3...)

ARRAY_AGG: собрать строки в массив

-- Собрать теги статей в один массив на пользователя
SELECT
    user_id,
    ARRAY_AGG(DISTINCT tag ORDER BY tag) AS all_tags
FROM user_articles
CROSS JOIN UNNEST(tags) AS tag
GROUP BY user_id;

-- Собрать ID связанных записей
SELECT
    category_id,
    ARRAY_AGG(id ORDER BY created_at) AS article_ids
FROM articles
GROUP BY category_id;

Функции для работы с массивами

-- Размер
ARRAY_LENGTH(arr, 1)      -- длина по измерению 1
CARDINALITY(arr)          -- общее число элементов

-- Поиск позиции
ARRAY_POSITION(arr, 'sql')  -- позиция первого вхождения (или NULL)

-- Все позиции
ARRAY_POSITIONS(arr, 'sql') -- массив позиций всех вхождений

-- Конкатенация
ARRAY_CAT(arr1, arr2)        -- объединить два массива

-- Сортировка
SELECT ARRAY(SELECT UNNEST(tags) ORDER BY 1) FROM articles WHERE id = 1;

-- Уникальные элементы
SELECT ARRAY(SELECT DISTINCT UNNEST(tags) FROM articles WHERE id = 1);

GIN-индекс для массивов

Без индекса поиск = ANY(tags) — Seq Scan. GIN-индекс ускоряет поиск по элементам:

CREATE INDEX idx_articles_tags ON articles USING gin(tags);

-- Теперь эти запросы используют индекс:
WHERE 'sql' = ANY(tags)     -- индекс
WHERE tags @> ARRAY['sql']  -- индекс
WHERE tags && ARRAY['sql', 'python']  -- индекс

-- Но НЕ используют:
WHERE tags[1] = 'sql'  -- позиционный доступ — Seq Scan

Массивы vs нормализованные таблицы vs JSONB

Используйте массивы, когда:

• Элементы простого типа (TEXT, INT, UUID)
• Порядок элементов важен
• Не нужны JOIN по элементам

Используйте нормализованную таблицу, когда:

• Элементы — сущности с атрибутами (теги с цветом, весом и т.д.)
• Нужны JOIN, агрегации, фильтры по атрибутам элементов

Практический пример: теги статей

-- Найти статьи с тегами sql И postgresql
SELECT title FROM articles
WHERE tags @> ARRAY['sql', 'postgresql'];

-- Найти статьи с хоть одним из тегов
SELECT title FROM articles
WHERE tags && ARRAY['sql', 'python'];

-- Статистика по тегам
SELECT
    tag,
    COUNT(*) AS articles,
    ARRAY_AGG(title ORDER BY title) AS article_titles
FROM articles
CROSS JOIN UNNEST(tags) AS tag
GROUP BY tag
ORDER BY articles DESC
LIMIT 10;

Итог

Массивы в PostgreSQL — удобный инструмент для хранения списков однородных данных. Ключевые операторы: