8.17. Типы диапазонов#

8.17. Типы диапазонов
8.17. Типы диапазонов
Назад НаверхГлава 8. Типы данныхНачало Далее

8.17. Типы диапазонов

8.17.1. Встроенные типы диапазона и многодиапазонные типы
8.17.2. Примеры
8.17.3. Инклюзивные и исключительные границы
8.17.4. Бесконечные (неограниченные) диапазоны
8.17.5. Ввод/вывод диапазона
8.17.6. Создание диапазонов и многодиапазонов
8.17.7. Дискретные типы диапазонов
8.17.8. Определение новых типов диапазонов
8.17.9. Индексирование
8.17.10. Ограничения на диапазоны

Типы диапазонов - это типы данных, представляющие диапазон значений некоторого элементарного типа (называемого подтипом диапазона). Например, диапазоны timestamp могут использоваться для представления временных интервалов, на которые зарезервирована переговорная комната. В этом случае тип данных - tsrange (сокращение от “диапазон времени”), а timestamp является подтипом. Подтип должен иметь полный порядок, чтобы было определено, находятся ли значения элементов внутри, до или после диапазона значений.

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

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

8.17.1. Встроенные типы диапазона и многодиапазонные типы

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

  • 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 для получения дополнительной информации.

8.17.2. Примеры

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);

-- Extract the upper bound
SELECT upper(int8range(15, 25));

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);

-- Is the range empty?
SELECT isempty(numrange(1, 5));

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

8.17.3. Инклюзивные и исключительные границы

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

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

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

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

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

Типы элементов, включающие понятие “infinity”, могут использовать их в качестве явных связанных значений. Например, в диапазонах временных меток [today,infinity] исключает специальное значение timestamp infinity, в то время как [today,infinity] включает его, как и [today,) и [today,].

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

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

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

(lower-bound,upper-bound)
(lower-bound,upper-bound]
[lower-bound,upper-bound)
[lower-bound,upper-bound]
empty

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

Lower-bound может быть строкой, которая является допустимым вводом для подтипа, или пустой, чтобы указать отсутствие нижней границы. Аналогично, upper-bound может быть строкой, которая является допустимым вводом для подтипа, или пустой, чтобы указать отсутствие верхней границы.

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

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

Примечание

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

Примеры: 

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

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

Примеры: 

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

8.17.6. Создание диапазонов и многодиапазонов

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

-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
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));

8.17.7. Дискретные типы диапазонов

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

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

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

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

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

Пользователи могут определить свои собственные типы диапазонов. Самая распространенная причина для этого - использование диапазонов над подтипами, которые не предоставляются среди встроенных типов диапазонов. Например, чтобы определить новый тип диапазона подтипа 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 для получения дополнительной информации о создании типов диапазонов.

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

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

CREATE INDEX reservation_idx ON reservation USING GIST (during);

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

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

8.17.10. Ограничения на диапазоны

В то время как 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")).

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

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

Назад Наверх Далее
8.16. Составные типы Начало 8.18. Типы доменов