Диапазонные типы данных

Диапазонные типы представляют диапазоны значений некоторого типа данных (он также называется подтипом диапазона). Например, диапазон типа timestamp может представлять временной интервал, когда зарезервирован зал заседаний. В данном случае типом данных будет tsrange (сокращение от «timestamp range»), а подтипом — timestamp. Подтип должен быть полностью упорядочиваемым, чтобы можно было однозначно определить, где находится значение по отношению к диапазону: внутри, до или после него.

Диапазонные типы полезны тем, что позволяют представить множество возможных значений в одной структуре данных и чётко выразить такие понятия, как пересечение диапазонов. Наиболее очевидный вариант их использования — применять диапазоны даты и времени для составления расписания, но также полезными могут оказаться диапазоны цен, интервалы измерений и т. д.

ВАЖНО: Один диапазон представляет единый непрерывный интервал без внутренних разрывов. Для хранения нескольких разрывных интервалов используются мультидиапазонные типы (PostgreSQL 14+).

Встроенные диапазонные типы

PostgreSQL предоставляет несколько встроенных диапазонных типов:

-- Целочисленные диапазоны
int4range — диапазон integer
int8range — диапазон bigint

-- Числовые диапазоны с плавающей точкой
numrange — диапазон numeric

-- Временные диапазоны
tsrange — диапазон timestamp без временной зоны
tstzrange — диапазон timestamp с временной зоной
daterange — диапазон date

Включение и исключение границ

Любой непустой диапазон имеет две границы, верхнюю и нижнюю, и включает все точки между этими значениями. В него также может входить точка, лежащая на границе, если диапазон включает эту границу. И наоборот, если диапазон не включает границу, считается, что точка, лежащая на этой границе, в него не входит.

В текстовой записи диапазона включение нижней границы обозначается символом «[», а исключением — символом «(». Для верхней границы включение обозначается аналогично, символом «]», а исключение — символом «)».

Для проверки, включается ли нижняя или верхняя граница в диапазон, предназначены функции lower_inc и upper_inc, соответственно.

Неограниченные (бесконечные) диапазоны

Нижнюю границу диапазона можно опустить и определить тем самым диапазон, включающий все значения, лежащие ниже верхней границы, например: (,3]. Подобным образом, если не определить верхнюю границу, в диапазон войдут все значения, лежащие выше нижней границы. Если же опущена и нижняя, и верхняя границы, такой диапазон будет включать все возможные значения своего подтипа. Указание отсутствующей границы как включаемой в диапазон автоматически преобразуется в исключающее; например, [,] преобразуется в (,). Можно воспринимать отсутствующие значения как плюс/минус бесконечность, но всё же это особые значения диапазонного типа, которые охватывают и возможные для подтипа значения плюс/минус бесконечность.

Для подтипов, в которых есть понятие «бесконечность», infinity может использоваться в качестве явного значения границы. При этом, например, в диапазон [today,infinity) с подтипом timestamp не будет входит специальное значение infinity данного подтипа, однако это значение будет входить в диапазон [today,infinity], как и в диапазоны [today,) и [today,].

Проверить, определена ли верхняя или нижняя граница, можно с помощью функций lower_inf и upper_inf, соответственно.

Базовое создание диапазонов

-- создание диапазонов различными способами
select '[1,10]'::int4range;                     -- от 1 включительно до 10 включительно
select '(1,10)'::int4range;                     -- от 1 не включительно до 10 не включительно
select '[2023-01-01,2023-12-31]'::daterange;    -- от 2023-01-01 включительно до 2023-12-31 включительно
select '[100,500)'::numrange;                   -- от 100 включительно до 500 не включительно

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

-- таблица для проектов
create table projects (
    id serial primary key,
    name varchar(100),
    execution_period tstzrange, --период реализации проекта
    budget_range numrange --диапазон бюджетной стоимости проекта
);

EXCLUDE

Ограничение-исключение, которое позволяет определить условие «непересечения» диапазонов.

-- таблица для бронирования номеров
create table hotel_reservations (
    id serial primary key,
    room_number integer,
    reservation_period daterange, --период резерва номера
    price_range numrange, --диапазон стоимости аренды
    exclude using gist (room_number with =, reservation_period with &&) 
    --проверяем, что один и тот же номер нельзя арендовать при пересечении диапазонов аренды
); 
/*
Так как для типа integer по умолчанию не определен класс операторов GiST, то 
нужно установить расширение btree_gist, которое добавляет поддержку GiST индексов 
для обычных скалярных типов данных:
*/
create extension if not exists btree_gist;

Важно: EXCLUDE особенно важен в многопользовательских системах, так как гарантирует отсутствие пересекающихся диапазонов даже при конкурентных INSERT/UPDATE.

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

-- получение границ диапазона
select lower('[1,10)'::int4range), upper('[1,10)'::int4range);

lower|upper|
-----+-----+
    1|   10|

-- проверка пустоты
select isempty('(1,1)'::int4range); -- true

-- извлечение границ
select lower_inc('[1,10]'::int4range), upper_inc('[1,10]'::int4range);

lower_inc|upper_inc|
---------+---------+
true     |false    |

--тип данных int4range не поддерживает включающую верхнюю границу при обычной записи. 
--если нужно получить 10, как верхнюю границу, то нужно указывать диапазон [1,11)

-- расширение диапазона
select range_merge('[1,5]'::int4range, '[3,10]'::int4range); --[1,11) 

-- разность диапазонов
select '[1,10]'::int4range - '[4,10]'::int4range; -- [1,4)

-- длина диапазона
select numrange(1.5, 10.2),
    upper(numrange(1.5, 10.2)) - lower(numrange(1.5, 10.2)) diff;

numrange  |diff    |
----------+--------+
[1.5,10.2)|     8.7|

Операторы сравнения и содержания

@> — содержит ли диапазон элемент или другой диапазон
select '[1,10]'::int4range @> 5; -- true
select '[1,10]'::int4range @> '[2,8]'::int4range; -- true

<@ — содержится ли элемент в диапазоне или диапазон в другом диапазоне
select 5 <@ '[1,10]'::int4range; -- true
select '[2,8]'::int4range <@ '[1,10]'::int4range; -- true

&& — пересекаются ли диапазоны
select '[1,5]'::int4range && '[3,7]'::int4range; -- true

<< — строго левее 
select '[1,5]'::int4range << '[6,10]'::int4range; -- true

>> — строго правее
select '[6,10]'::int4range >> '[1,5]'::int4range; -- true

&< — не расширяется правее правого диапазона 
select '[1,5]'::int4range &< '[5,10]'::int4range; -- true

&> — не расширяется левее левого диапазона 
select '[5,10]'::int4range &> '[1,5]'::int4range; -- true

-|- — примыкает (смежный)
select '[1,5]'::int4range -|- '[5,10]'::int4range; -- true

Создание пользовательских диапазонных типов

-- создаем enum тип для приоритетов
create type priority_level as enum ('very low', 'low', 'medium', 'high', 'very high');

-- создаем диапазонный тип на основе enum
create type priority_range as range (
    subtype = priority_level
);

-- используем в таблице
create table tasks (
    id serial primary key,
    name varchar(100),
    allowed_priority priority_range
);

--внесем несколько строк
insert into tasks (name, allowed_priority) values
    ('обычная задача', '[very low,high]'),
    ('срочная задача', '[medium,very high]');

--получим задачи, которые включают значение high
select *
from tasks
where allowed_priority @> 'high'::priority_level;

id|name          |allowed_priority    |
--+--------------+--------------------+
 1|обычная задача|["very low",high]   |
 2|срочная задача|[medium,"very high"]|

Конструкторы диапазонов

Помимо приведения строк (например, '[1,10]'::int4range), PostgreSQL предоставляет функции-конструкторы для создания диапазонных значений. Синтаксис:

диапазонный_тип( нижняя_граница, верхняя_граница [, текст_формат] )

Параметр текст_формат - строка, состоящая из одного или двух символов, определяющих включение/исключение границ: '[]' - обе границы включены '[)' - нижняя включена, верхняя исключена '(]' - нижняя исключена, верхняя включена '()' - обе границы исключены Если параметр формата опущен, по умолчанию подразумевается '[)' (полуоткрытый интервал) для всех типов. Важно: PostgreSQL внутри всегда хранит диапазоны в полуоткрытом виде [), поэтому '[]' нормализуется в '[)'.

Примеры для целочисленных диапазонов:

-- без формата
select int4range(1, 10);          -- [1,10)

-- с форматом
select int4range(1, 10, '[)');    -- [1,10)
select int4range(1, 10, '[]');    -- [1,11)
select int4range(1, 10, '()');    -- [2,10)

-- открытые границы
select int4range(NULL, 10);       -- (,10)
select int4range(1, NULL);        -- [1,)
--NULL означает бесконечность только в семантике диапазона, но не является значением "infinity"

Примеры для дат

select daterange('2024-01-01', '2024-12-31', '[]');   -- [2024-01-01,2025-01-01)
select daterange('2024-01-01', '2024-12-31', '[)');   -- [2024-01-01,2024-12-31)

Примеры для числовых диапазонов

select numrange(1.5, 10.2, '[]');   -- [1.5,10.2]
select numrange(1.5, 10.2, '[)');   -- [1.5,10.2)

В некоторых случаях конструкторы:

• безопаснее строкового кастинга при динамическом формировании значений
• удобная работа с открытыми границами через NULL

Заключение

Диапазонные типы данных в PostgreSQL позволяют работать с интервалами значений, что дает возможность:

• эффективно хранить и обрабатывать временные периоды, числовые диапазоны
• обеспечивать целостность данных через ограничения исключения
• создавать специализированные типы для конкретных бизнес-потребностей
• для диапазонных типов чаще всего используются GiST или SP-GiST индексы.

Независимо от способа создания и реализации, PostgreSQL гарантирует единое поведение операций над диапазонами за счёт нормализации границ.

Освоение диапазонных типов открывает новые возможности для создания надежных и эффективных приложений, работающих с интервальными данными.

При дальнейшем углублении в SQL вы можете создавать собственные диапазонные типы с применением процедурных языков и моделировать диапазоны с прерываниями.