Реляционная модель учит раскладывать данные по плоским колонкам, но в postgresql есть целое семейство составных типов данных, которые позволяют хранить в одной ячейке несколько значений сразу. Массив тегов, интервал дат брони, фиксированный список статусов, упакованный адрес - все это можно описать одним столбцом, не плодя отдельные справочники. В этом уроке разберем четыре таких инструмента: массивы, диапазоны (range), перечисления (enum) и составные (row) типы. Главное - понять не только синтаксис, но и когда такой тип реально упрощает схему, а когда он маскирует плохой дизайн и тянет вас в болото.
Как это работает
Массив - это упорядоченный набор значений одного базового типа: integer[], text[], даже двумерный integer[][]. Внутри Postgres хранит его как единый объект с границами по каждому измерению. Индексация в SQL начинается с единицы, а не с нуля - это первая ловушка для тех, кто пришел из программирования. Массив удобен, когда порядок важен и набор короткий, например список номеров мест в одном бронировании.
Диапазон описывает непрерывный отрезок значений: int4range для целых, numrange для чисел, tsrange и tstzrange для времени, daterange для дат. У границы есть включенность: запись [10,20) означает от 10 включительно до 20 не включая. Сила range в том, что Postgres знает геометрию отрезка: умеет проверять пересечение оператором &&, вложенность, соседство. Это решает классическую задачу бронирования без перекрытий гораздо чище, чем пара колонок from и to с ручными проверками.
Перечисление (enum) - это собственный тип с фиксированным списком текстовых меток в заданном порядке: например статус билета. Внутри значение хранится компактно (4 байта на ссылку), сравнивается быстро, а порядок сортировки определяется порядком объявления меток, а не алфавитом. Enum защищает от опечаток на уровне типа: вставить статус которого нет в списке просто не получится.
Составной тип - это именованная строка из нескольких полей, по сути своя мини-таблица как тип данных. Каждая таблица автоматически порождает одноименный составной тип, но можно создать и отдельный через CREATE TYPE. Удобно, когда несколько полей логически слиплись в одну сущность, например структура адреса.
SQL и примеры
Массивы используем на демобазе Авиаперевозки. В таблице seats место описано номером, но соберем номера мест по салону самолета в массив:
Код: Выделить всё
SELECT aircraft_code,
array_agg(seat_no ORDER BY seat_no) AS seats
FROM seats
GROUP BY aircraft_code;
Код: Выделить всё
SELECT unnest(ARRAY['A','B','C']) AS letter;
Код: Выделить всё
SELECT ARRAY[1,2,3] @> ARRAY[2] AS contains_two,
ARRAY[1,2] && ARRAY[2,9] AS overlaps,
cardinality(ARRAY[5,6,7]) AS len;
Диапазоны покажем на интервалах. Построим для каждого рейса временной отрезок от вылета до прилета и проверим пересечение с заданным окном:
Код: Выделить всё
SELECT flight_id,
tstzrange(scheduled_departure, scheduled_arrival) AS window
FROM flights
WHERE tstzrange(scheduled_departure, scheduled_arrival)
&& tstzrange('2017-08-15 00:00+03','2017-08-15 06:00+03');
Код: Выделить всё
CREATE TABLE room_booking (
room_id int,
during tstzrange,
EXCLUDE USING gist (room_id WITH =, during WITH &&)
);
Перечисление: заведем тип статуса и используем порядок меток.
Код: Выделить всё
CREATE TYPE ticket_state AS ENUM ('booked','checked_in','boarded','cancelled');
SELECT 'boarded'::ticket_state > 'booked'::ticket_state AS later;
Составной тип соберем для адреса:
Код: Выделить всё
CREATE TYPE addr AS (city text, street text, zip text);
SELECT (ROW('Moscow','Tverskaya','101000')::addr).city AS city;
Частые грабли
- Индексация массива с единицы: arr[1] - первый элемент, arr[0] вернет NULL, а не ошибку.
- Массив как замена связи многие-ко-многим: искать, джойнить и поддерживать ссылочную целостность по элементам массива неудобно, для этого нужна таблица связи.
- Путаница с границами range: [a,b) и [a,b] это разные множества, по умолчанию нижняя включается, верхняя нет.
- Пустой и NULL-диапазон - разные вещи: empty не содержит ничего, а NULL это отсутствие значения.
- Удаление метки enum невозможно штатно, а порядок меток фиксируется при создании - меняется только через ALTER TYPE с оговорками.
- ADD VALUE до PG 12 нельзя было применять внутри транзакционного блока вместе с использованием значения.
- Для оператора && по диапазонам нужен GiST-индекс, обычный btree пересечение не ускорит.
- Составной тип усложняет фильтры и индексы по отдельному вложенному полю - иногда проще обычные колонки.
- Создайте таблицу events(id int, tags text[], slot tstzrange).
- Вставьте 3 строки с разными тегами и пересекающимися и непересекающимися слотами времени.
- Запросом найдите события, где tags содержит 'sql' через оператор @>.
- Через unnest разверните теги в строки и посчитайте частоту каждого тега.
- Найдите пары событий, чьи слоты пересекаются, используя && и self-join по id1 < id2.
- Создайте тип priority AS ENUM ('low','mid','high'), добавьте колонку и отсортируйте события по приоритету.
- Добавьте exclusion constraint, запрещающий пересечение слотов, и проверьте, что вставка-дубль падает.
- С какого индекса начинается обращение к элементам массива в SQL и что вернет обращение за границу?
- Чем отличаются операторы @> и && для массивов и для диапазонов?
- Как граница диапазона [a,b) отличается от (a,b] по составу множества?
- Почему сортировка enum не алфавитная и от чего зависит порядок?
- Какие ограничения есть у изменения enum: что можно, а что нельзя сделать без пересоздания типа?
- В каких случаях массив или составной тип лучше заменить отдельной таблицей?
