PostgreSQL: массивы и range-типы

PostgreSQL умеет хранить в одной колонке несколько значений (массивы) и интервалы (range-типы). Это редкие, но иногда правильные инструменты — особенно когда альтернатива (отдельная таблица) усложняет схему ради формальности.

1. Массивы

CREATE TABLE article (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title text   NOT NULL,
    tags  text[] NOT NULL DEFAULT '{}'
);

INSERT INTO article (title, tags) VALUES ('Заголовок', ARRAY['ddd', 'pg', 'архитектура']);

SELECT * FROM article WHERE 'pg' = ANY(tags);
SELECT * FROM article WHERE tags @> ARRAY['ddd'];     -- содержит все указанные
SELECT * FROM article WHERE tags && ARRAY['ddd'];     -- пересекается

Когда массив оправдан

PG-T-070 Массив уместен, когда:

• Это простой набор скалярных значений (теги, разрешения, локали поддержки).
• Размер ограничен небольшим числом элементов (десятки максимум).
• Нет отдельной идентичности у элемента — никто не редактирует «третий тег», только заменяет весь набор.
• Не нужно ссылаться на элементы из других таблиц.

Когда массив — антипаттерн

PG-T-071 Массив неуместен, когда:

• У каждого элемента есть свои атрибуты — [{name, value, weight}, ...]. Это таблица, не массив.
• Нужно ссылаться на конкретный элемент извне (article.tags[2] нестабильно при удалении).
• Ожидается тысячи элементов на запись.
• Элементы независимо обновляются разными запросами / транзакциями.

Контр-пример (плохо):

CREATE TABLE order_doc (
    id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    items jsonb[]                -- массив из jsonb. Заводов нет, статусов нет, валидации нет.
);

Лучше:

CREATE TABLE order_doc (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE order_item (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id  bigint NOT NULL REFERENCES order_doc(id) ON DELETE CASCADE,
    sku       varchar(50) NOT NULL,
    quantity  integer NOT NULL CHECK (quantity > 0),
    price     numeric(15, 2) NOT NULL
);

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

CREATE INDEX article_tags_gin ON article USING gin (tags);

-- ускорит:
SELECT * FROM article WHERE tags @> ARRAY['ddd'];
SELECT * FROM article WHERE tags && ARRAY['ddd', 'pg'];
SELECT * FROM article WHERE 'ddd' = ANY(tags);

Java-сторона

jOOQ генерирует String[] / Integer[] для array-колонок. Можно прокидывать как List<String> через converter — обычно это удобнее в коде.

2. Range-типы

PostgreSQL имеет встроенные range-типы:

Тип	Содержит
int4range	интервал integer
int8range	интервал bigint
numrange	интервал numeric
daterange	интервал date
tsrange	интервал timestamp (без TZ)
tstzrange	интервал timestamptz

PG-T-072 Range-тип оправдан, когда сущность сама по себе — это интервал.

Хорошие случаи:

• Период действия тарифа / промокода / роли (с какого по какое действует).
• Бронирование (дата-время заезда — выезда).
• Цена с историей (когда стоила эту цену).
• Возрастные ограничения (int4range(18, 65)).

Сравните «двух колонок» с «одной range-колонкой»:

-- две колонки
CREATE TABLE tariff_v1 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    valid_from  timestamptz NOT NULL,
    valid_to    timestamptz                                       -- NULL = без ограничения
);

-- range
CREATE TABLE tariff_v2 (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    validity    tstzrange NOT NULL DEFAULT tstzrange(now(), 'infinity', '[)')
);

Что даёт range-тип

1. Атомарная семантика интервала — один тип со встроенным включающим/исключающим краем ([), (], [], ()).

2. Операторы пересечения:

-- две колонки — много кода и легко ошибиться:
SELECT * FROM tariff_v1
WHERE valid_from < '2026-05-07T12:00:00+00'
  AND (valid_to IS NULL OR valid_to > '2026-05-07T12:00:00+00');

-- range — кратко и однозначно:
SELECT * FROM tariff_v2
WHERE validity @> timestamptz '2026-05-07T12:00:00+00';

Оператор	Что делает
@>	range содержит точку или другой range
&&	два range пересекаются
-|-	соприкасаются (соседние)
<< / >>	строго слева / справа

3. EXCLUDE constraint — гарантия непересечения (фундамент для бронирований).

EXCLUDE для непересечений

Это уникальная возможность PostgreSQL. На «двух колонках» сделать «брони не пересекаются» — только триггерами с риском состояния гонки. На range — одной декларацией:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE booking (
    id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    room_id   bigint NOT NULL,
    period    tstzrange NOT NULL,
    EXCLUDE USING gist (room_id WITH =, period WITH &&)
);

INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-07 14:00, 2026-05-09 12:00)');
INSERT INTO booking (room_id, period) VALUES (1, '[2026-05-08 10:00, 2026-05-10 12:00)');
-- ERROR: conflicting key value violates exclusion constraint

PG-T-073 Если бизнес-задача — «эти интервалы для одного ключа не пересекаются», используем EXCLUDE constraint, а не приложение.

Альтернатива на стороне приложения — гонка и двойная бронь при параллельных запросах. Ограничение на уровне БД защищает даже при одновременных вставках.

Открытые/закрытые края

Граничный синтаксис критичен:

• [a, b) — a включён, b исключён (стандарт для дат-интервалов: с 1 марта по 31 марта = [2026-03-01, 2026-04-01)).
• [a, b] — оба включены.
• (a, b) — оба исключены.
• [a, +infinity) — без верхней границы.

PG-T-074 По умолчанию используем [) — это даёт корректное && без перекрытия концов. Иначе бронь «с 14:00 до 16:00» и «с 16:00 до 18:00» считаются пересекающимися.

Multirange (PG14+)

PG14 ввёл multirange — массив непересекающихся range:

CREATE TABLE schedule (
    id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    avail  tstzmultirange NOT NULL    -- массив периодов доступности
);

Полезно для расписаний с несколькими окнами.

Java-сторона

jOOQ генерирует Range<T> (свой тип). Удобно обернуть в Java-record:

public record ValidityPeriod(Instant from, Instant to) {
    public boolean contains(Instant t) { return !t.isBefore(from) && t.isBefore(to); }
}

И converter Range<OffsetDateTime> ↔ ValidityPeriod.

---

Чек-лист на ревью

• [ ] Массив используется только для простых, ограниченных по числу значений без идентичности (теги, флаги).
• [ ] Если у элементов массива есть атрибуты или они независимо обновляются — это отдельная таблица.
• [ ] При фильтрации @> / && по массиву — GIN-индекс.
• [ ] Сущности-интервалы (тарифы, брони, периоды действия) хранятся в range-типе, не в двух отдельных колонках.
• [ ] При требовании «не пересекаются» — EXCLUDE constraint, не код приложения.
• [ ] Граничный синтаксис [) если в задаче подразумевается «верхняя граница не включена» (стандарт для дат).

---

Связанные

• JSONB — альтернатива массиву объектов (часто хуже).
• Антипаттерны.