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

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

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

Для каждого диапазонного типа имеется соответствующий мультидиапазонный тип. Мультидиапазон представляет собой упорядоченный список несмежных, непустых и отличных от NULL диапазонов. Большинство диапазонных операторов работают и с мультидиапазонами, а кроме того у мультидиапазонных типов есть несколько собственных функций.

Встроенные диапазонные и мультидиапазонные типы

QHB поставляется со следующими встроенными диапазонными типами:

  • int4range — диапазон подтипа integer, int4multirange — соответствующий мультидиапазон

  • int8range — диапазон подтипа bigint, int8multirange — соответствующий мультидиапазон

  • numrange — диапазон подтипа numeric, nummultirange — соответствующий мультидиапазон

  • tsrange — диапазон подтипа timestamp without time zone, tsmultirange — соответствующий мультидиапазон

  • tstzrange — диапазон подтипа timestamp with time zone, tstzmultirange — соответствующий мультидиапазон

  • daterange — диапазон подтипа date, datemultirange — соответствующий мультидиапазон

Кроме того, вы можете определить собственные диапазонные типы; подробную информацию см. в описании команды CREATE TYPE.

Примеры

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Вхождение
SELECT int4range(10, 20) @> 3;

-- Перекрытие
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- Получение верхней границы
SELECT upper(int8range(15, 25));

-- Вычисление пересечения
SELECT int4range(10, 20) * int4range(15, 25);

-- Является ли диапазон пустым?
SELECT isempty(numrange(1, 5));

Полный список операторов и функций для диапазонных типов приведен в таблицах Диапазонные операторы и Диапазонные функции.

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

Каждый непустой диапазон имеет две границы: нижнюю и верхнюю. Все точки между этими значениями включены в диапазон. Включение границы означает, что сама граничная точка также включена в диапазон, а исключение — что она не включена.

В текстовой записи диапазона включение нижней границы обозначается символом "[", а исключение — символом "(". Аналогично включение верхней границы обозначается символом "]", а исключение — символом ")". (Подробную информацию см. в подразделе Ввод/вывод диапазонов).

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

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

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

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

Функции lower_inf и upper_inf проверяют, опущена ли нижняя или верхняя границы диапазона, соответственно.

Ввод/вывод диапазонов

Вводимое значение диапазона должно записываться по одному из следующих шаблонов:

(нижняя-граница,верхняя-граница)
(нижняя-граница,верхняя-граница]
[нижняя-граница,верхняя-граница)
[нижняя-граница,верхняя-граница]
empty

Тип скобок (круглые или квадратные) указывает, включены ли в диапазон нижняя и верхняя границы, как описано выше. Обратите внимание, что последний шаблон содержит только слово empty и обозначает пустой диапазон (диапазон, не содержащий точек).

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

Каждое значение границы можно заключить в кавычки ("). Это необходимо делать, если значение границы содержит круглые или квадратные скобки, запятые, кавычки или обратный слэш, поскольку иначе эти символы будут рассматриваться как часть синтаксиса диапазона. Чтобы поместить в значение диапазона, заключенное в кавычки, кавычку или обратный слэш, нужно вставить перед ними перед ним обратный слэш. (Кроме того, пара кавычек в значении диапазона, заключенного в кавычки, используется для представления символа одинарной кавычки аналогично правилам для апострофов в строковых литералах SQL.) Или же можно обойтись без кавычек и использовать обратный слэш для экранирования всех символов в данных, которые в противном случае могут быть восприняты как синтаксис диапазона. Кроме того, чтобы задать в качестве значения границы пустую строку, следует написать "", так как пустая строка означает отсутствие границы.

Пробел допускается до и после значения диапазона, но любой пробел внутри круглых или квадратных скобок воспринимается как часть значения нижней или верхней границы. (Хотя в некоторых подтипах такие пробелы могут игнорироваться.)

Примечание
Эти правила очень похожи на правила записи значений полей в литералах составного типа. Дополнительные замечания см. в разделе Синтаксис вводимых и выводимых значений составного типа.

Примеры:

-- в диапазон включается 3, не включается 7 и включаются все точки между ними
SELECT '[3,7)'::int4range;

-- в диапазон не включаются ни 3, ни 7, но включаются все точки между ними
SELECT '(3,7)'::int4range;

-- в диапазон включается только значение 4
SELECT '[4,4]'::int4range;

-- диапазон не включает никаких точек (нормализация заменит эту запись на 'empty')
SELECT '[4,4)'::int4range;

Входное значение для мультидиапазона заключается в фигурные скобки ({ и }) и содержит ноль или более допустимых диапазонов, разделенных запятыми. До и после скобок и запятых допускаются пробельные символы. Синтаксис сделан похожим на синтаксис массивов, хотя мультидиапазоны гораздо проще: у них всего одна размерность и их содержимое не нужно заключать в кавычки. (Однако, как показано выше, границы диапазонов в них могут заключаться в кавычки.)

Примеры:

SELECT '{}'::int4multirange;
SELECT '{[3,7)}'::int4multirange;
SELECT '{[3,7), [8,9)}'::int4multirange;

Конструирование диапазонов и мультидиапазонов

Для каждого типа диапазонов имеется функция-конструктор с тем же именем, что и данный тип. Использование этого конструктора зачастую более удобно, чем запись литеральной константы диапазона, поскольку это избавляет от необходимости заключать значения границ в дополнительные кавычки. Функция-конструктор может принимать два или три аргумента. Вариант с двумя аргументами создает диапазон в стандартной форме (нижняя граница включена, верхняя граница исключена), тогда как вариант с тремя аргументами создает диапазон, у которого включение границ определяется третьим аргументом. Этот аргумент должен содержать одну из строк: "()", "(]", "[)" или "[]". Например:

-- Полная форма: нижняя граница, верхняя граница и текстовый аргумент, определяющий
-- включение/исключение границ.
SELECT numrange(1.0, 14.0, '(]');

-- Если третий аргумент опущен, подразумевается '[)'.
SELECT numrange(1.0, 14.0);

-- Хотя здесь указывается '(]', при выводе значение будет преобразовано в
-- каноническую форму, так как int8range — это тип дискретного диапазона (см. ниже).
SELECT int8range(1, 14, '(]');

-- Если указать NULL вместо любой из границ, этой границы у диапазона не будет.
SELECT numrange(NULL, 2.2);

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

SELECT nummultirange();
SELECT nummultirange(numrange(1.0, 14.0));
SELECT nummultirange(numrange(1.0, 14.0), numrange(20.0, 25.0));

Типы дискретных диапазонов

Дискретный диапазон — это диапазон, для подтипа которого четко определен «шаг», как, например, для типов integer и date. Элементы этих типов можно назвать соседними, когда между ними нет никаких допустимых значений. Это отличает дискретные диапазоны от непрерывных, в которых всегда (или почти всегда) можно найти другие значения элемента между двумя данными. Например, непрерывными является диапазон с подтипом numeric или timestamp. (Хотя timestamp имеет ограниченную точность и поэтому теоретически может рассматриваться как дискретный, лучше считать его непрерывным, поскольку обычно размер его шага не представляет интереса.)

Другой способ определения типа дискретного диапазона состоит в том, что в нем существует четкое представление о «следующем» или «предыдущем» элементе для каждого значения. Зная это, можно преобразовывать границы диапазона из включаемых в исключаемые, выбрав следующий или предыдущий элемент вместо заданного изначально. Например, в диапазоне целочисленного типа [4,8] и (3,9) обозначают одно и то же множество значений; но для диапазона числового типа это не так.

Тип дискретного диапазона должен иметь функцию канонизации, которая учитывает желаемый размер шага для данного подтипа. Функция канонизации отвечает за преобразование равнозначных значений типа диапазона в единичные представления, в частности, согласованность включаемых и исключаемых границ. Если функция канонизации не указана, то диапазоны с различным форматированием всегда будут считаться разными, даже если в действительности они представляют одно и то же множество значений.

Для встроенных диапазонных типов int4range, int8range и daterange используется каноническая форма, которая включает нижнюю границу и исключает верхнюю, то есть [). Однако для пользовательских типов можно использовать другие соглашения.

Определение новых диапазонных типов

Пользователи могут определять собственные диапазонные типы. Наиболее распространенная причина для этого — использование диапазонов с подтипами, для которых нет встроенных диапазонных типов. Например, можно определить новый диапазонный тип для подтипа float8:

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;

Поскольку float8 не имеет значимого «шага», функция канонизации в этом примере не задана.

Когда вы определяете собственный диапазон, вы автоматически получаете соответствующий мультидиапазонный тип.

Определение собственного диапазонного типа также позволяет выбрать другой класс оператора B-дерева или правило сортировки для его подтипа, чтобы изменить порядок сортировки, определяющий, какие значения попадают в данный диапазон.

Если считается, что значения подтипа дискретные, а не непрерывные, в команде CREATE TYPE следует указать функцию canonical. Эта функция принимает на вход значение диапазона и должна возвращать равнозначное значение, у которого могут быть другие границы и форматирование. Для двух диапазонов, представляющих одно множество значений, например целочисленные диапазоны [1, 7] и [1, 8), функция канонизации должна выдавать одинаковый результат. Неважно, какое представление вы выберете как каноническое при условии, что два равнозначных значения с разным форматированием всегда преобразуются в одно значение с одинаковым форматированием. Кроме настройки формата включаемых/исключаемых границ функция канонизации также может округлять граничные значения, если желаемый размер шага превышает емкость хранения подтипа. Например, в диапазонном типе для подтипа timestamp можно определить размер шага, равный часу, тогда функция канонизации должна будет округлить границы, не кратные часу, или, возможно, выдать вместо этого ошибку.

Кроме того, для любого диапазонного типа, предназначенного к использованию с индексами GiST или SP-GiST, должна быть определена разница подтипов, или функция subtype_diff. (Индекс будет работать и без subtype_diff, но, скорее всего, будет гораздо менее эффективен.) Эта функция принимает на вход два значения подтипа и возвращает их разницу (то есть X минус Y) в значении типа float8. В примере выше можно использовать функцию float8mi, которая является нижележащей версией обычного оператора «минус» для типа float8, но для другого подтипа может понадобиться преобразование. Кроме того, для представления разницы в числовом виде может потребоваться творческий подход. Функция subtype_diff должна, насколько это возможно, согласовываться с порядком сортировки, подразумеваемым выбранным классом оператора и правилами сортировки; то есть ее результат должен быть положительным, если, согласно порядку сортировки, первый аргумент больше второго.

Менее упрощенный пример функции subtype_diff:

CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;

CREATE TYPE timerange AS RANGE (
    subtype = time,
    subtype_diff = time_subtype_diff
);

SELECT '[11:10, 23:00]'::timerange;

Дополнительную информацию о создании диапазонных типов см. в описании команды CREATE TYPE.

Индексирование

Для столбцов с диапазонным типом можно создать индексы GiST и SP-GiST. Например, так создается индекс GiST:

CREATE INDEX reservation_idx ON reservation USING GIST (during);

Индекс GiST или SP-GiST может ускорять запросы с участием следующих диапазонных операторов: =, &&, <@, @>, <<, >>, -|-, &< и &>. Индекс GiST для мультидиапазонов может ускорить запросы, задействующие один набор мультидиапазонных операторов. Индекс GiST для диапазонов и индекс GiST для мультидиапазонов также может ускорить запросы, задействующие следующие межтиповые операторы, преобразующие диапазоны в мультидиапазоны и обратно: &&, <@, @>, <<, >>, -|-, &< и &>. Дополнительную информацию см. в таблице Диапазонные операторы.

Кроме того, для таких столбцов можно создать индексы В-деревья или хэш-индексы. Для индексов таких типов полезна, собственно, только одна операция диапазона — равенство. Для значений диапазона определен порядок сортировки B-деревьев с соответствующими операторами < и >, но этот порядок довольно произвольный и обычно бесполезен в реальном мире. Поддержка B-деревьев и хэшей диапазонными типами предназначена в первую очередь для внутренней сортировки и хэширования при выполнении запросов, а не для создания собственно индексов.

Ограничения для диапазонов

Тогда как для скалярных значений UNIQUE является естественным ограничением, для диапазонных типов оно обычно не подходит. Более подходящими чаще всего являются ограничения-исключения (см. CREATE TABLE ... CONSTRAINT ... EXCLUDE). Такие ограничения позволяют задавать для диапазонов такие условия, как, например, «непересечение». Например:

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING GIST (during WITH &&)
);

Это ограничение не позволит одновременно сохранить в таблице перекрывающиеся значения:

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).
-- ОШИБКА:  конфликтующее значение ключа нарушает ограничение-исключение "reservation_during_excl"
-- ПОДРОБНОСТИ: Ключ (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00"))
-- конфликтует с существующим ключом (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

Для максимальной гибкости можно комбинировать ограничения-исключения для диапазонов с ограничениями-исключениями для простых скалярных типов данных, которые определяются расширением btree_gist. Например, после установки btree_gist следующее ограничение не будет допускать перекрывающиеся диапазоны, только если номера переговорных тоже совпадают:

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).
-- ОШИБКА:  конфликтующее значение ключа нарушает ограничение-исключение
-- "room_reservation_room_during_excl"
-- ПОДРОБНОСТИ:  Ключ (room, during)=(123A, [ 2010-01-01 14:30:00,
-- 2010-01-01 15:30:00 )) конфликтует
-- с существующим ключом (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1

Copyright © KVANTOM LLC, 2019-2023