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

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

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

-- Содержание (containment)
SELECT int4range(10, 20) @> 3;

-- Пересечение (overlaps)
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));

См. Таблица 2.6.56 и Таблица 2.6.58 для полных списков операторов и функций для типов диапазонов.

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

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

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

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

Базовые типы, имеющие понятие «бесконечности», могут использовать их в качестве явных граничных значений. Например, для диапазонов timestamp, [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 имеет ограниченную точность и, следовательно, теоретически может рассматриваться как дискретный, лучше считать его непрерывным, поскольку размер шага обычно не представляет интереса.)

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

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

Встроенные типы диапазонов 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 также могут быть созданы для столбцов таблиц типов мультидиапазонов. Например, чтобы создать индекс GiST:

CREATE INDEX reservation_idx ON reservation USING GIST (during);

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

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

Хотя UNIQUE является естественным ограничением для скалярных значений, оно обычно не подходит для типов диапазонов. Вместо этого ограничение исключения (exclusion) часто более уместно (см. 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")).

Вы можете использовать расширение 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")).

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