Типы данных

• Числовые Типы
  • Целочисленные типы
  • Числа произвольной точности
  • Типы с плавающей точкой
  • Серийные типы
• Денежные Типы
• Символьные типы
• Двоичные типы данных
  • «Шестнадцатеричный» формат bytea
  • Экранированный формат bytea
• Типы даты/времени
  • Ввод даты / времени
    • Даты
    • Время
    • Отметки времени
    • Специальные значения
  • Формат вывода типов дата/время
  • Часовые пояса
  • Формат ввода интервалов
  • Формат вывода интервалов
• Логический тип
• Перечисляемые типы
  • Объявление перечислимых типов
  • Порядок
  • Надежность типа
  • Детали реализации
• Геометрические типы
  • Точки
  • Линии
  • Отрезки линии
  • Рамки (boxes)
  • Пути
  • Полигоны
  • Круги
• Типы сетевых адресов
  • inet
  • cidr
  • inet и cidr
  • macaddr
  • macaddr8
• Типы битовых строк
• Типы текстового поиска
  • tsvector
  • tsquery
• Тип UUID
• Тип XML
  • Создание значений XML
  • Обработка кодировки
  • Доступ к значениям XML
• Типы JSON
  • Синтаксис ввода и вывода JSON
  • Разработка документов JSON
  • Анализ вложений и наличия в jsonb
  • Индексация jsonb
  • Трансформации
  • Тип jsonpath
• Массивы
  • Объявление типов массивов
  • Ввод значений массива
  • Доступ к массивам
  • Модификация массивов
  • Поиск в массивах
  • Синтаксис ввода и вывода массива
• Составные типы
  • Декларация составных типов
  • Построение составных значений
  • Доступ к составным типам
  • Модификация составных типов
  • Использование составных типов в запросах
  • Синтаксис ввода и вывода составного типа
• Типы диапазонов
  • Типы встроенных диапазонов
  • Примеры
  • Инклюзивные и эксклюзивные границы
  • Бесконечные (неограниченные) диапазоны
  • Диапазон ввода/вывода
  • Построение рядов
  • Типы дискретных диапазонов
  • Определение новых типов диапазона
  • Индексирование
  • Ограничения для диапазонов
• Типы доменов
• Типы идентификаторов объектов
• Тип pg_lsn
• Псевдо-типы

QHB имеет богатый набор собственных типов данных, доступных пользователям. Пользователи могут добавлять новые типы в QHB с помощью команды CREATE TYPE.

В Таблице 1 показаны все встроенные типы данных общего назначения. Большинство альтернативных имен, перечисленных в столбце «Псевдонимы», являются именами, используемыми внутри QHB по историческим причинам. Кроме того, доступны некоторые используемые или устаревшие типы, но они не перечислены здесь.

Таблица 1. Типы данных

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

Числовые Типы

Числовые типы состоят из двух-, четырех- и восьмибайтовых целых чисел, четырех- и восьмибайтовых чисел с плавающей запятой и десятичных дробей с выбираемой точностью. В Таблице 2 перечислены доступные типы.

Таблица 2. Числовые Типы

Синтаксис констант для числовых типов описан в разделе Константы. Числовые типы имеют полный набор соответствующих арифметических операторов и функций. Обратитесь к главе Функции и операторы за дополнительной информацией. В следующих разделах подробно описаны типы.

Целочисленные типы

Типы smallint, integer и bigint хранят целые числа, то есть числа без дробных компонент, различных диапазонов. Попытки сохранить значения за пределами допустимого диапазона приведут к ошибке.

Тип integer является наиболее распространенным, поскольку он обеспечивает наилучший баланс между диапазоном, размером хранилища и производительностью. Тип smallint обычно используется только в том случае, если объем дискового пространства выше. Тип bigint предназначен для использования, когда диапазон типа integer недостаточен.

В SQL определены только целочисленные типы integer (или int), smallint и bigint. Имена типов int2, int4 и int8 являются расширениями, которые также используются некоторыми другими системами баз данных SQL.

Числа произвольной точности

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

Следующие термины используются ниже: Точность (precision) числа — это общее количество значащих цифр во всем числе, то есть количество цифр по обеим сторонам десятичной точки. Масштаб (scale) числа — это количество десятичных цифр в дробной части, справа от десятичной точки. Таким образом, число 23,5141 имеет точность 6 и масштаб 4. Целые числа можно считать имеющими нулевой масштаб.

Можно настроить как максимальную точность, так и максимальный масштаб числового столбца. Чтобы объявить столбец числового типа, используйте синтаксис:

NUMERIC(precision, scale)

Точность должна быть положительной, масштаб должен быть неотрицательным. Альтернативный вариант:

NUMERIC(precision)

устанавливает масштаб 0. Форма:

NUMERIC

без какой-либо точности или масштаба создает столбец, в котором могут быть сохранены числовые значения любой точности и масштаба, вплоть до предела реализации по точности. Столбец такого типа не будет приводить входные значения к какому-либо конкретному масштабу, тогда как numeric столбцы с объявленным масштабом будут приводить входные значения к этому масштабу. (Стандарт SQL требует, чтобы масштаб по умолчанию был равен 0, что соответствует приведению к целочисленной точности. Если вы беспокоитесь о переносимости, всегда указывайте точность и масштаб явно).

Заметка
Максимально допустимая точность, если она явно указана в объявлении типа, составляет 1000; NUMERIC без указанной точности подпадает под ограничения, описанные в Таблице 2.

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

Числовые значения физически сохраняются без каких-либо дополнительных начальных или конечных нулей. Таким образом, заявленная точность и масштаб столбца являются максимальными, а не фиксированными распределениями. (В этом смысле числовой тип больше похож на varchar(n) чем на char(n)). Фактическое требование к хранилищу составляет два байта для каждой группы из четырех десятичных разрядов, плюс три-восемь байтов служебных данных.

В дополнение к обычным числовым значениям, числовой тип допускает специальное значение NaN, означающее «не число». Любая операция с NaN приводит к другому NaN. При записи этого значения в качестве константы в команду SQL вы должны заключать в кавычки, например, UPDATE table SET x = 'NaN'. При вводе строка NaN распознается без учета регистра.

Заметка
В большинстве реализаций концепции «не число» NaN не считается равным любому другому числовому значению (включая NaN). Чтобы разрешить сортировку и использование числовых значений в древовидных индексах, QHB рассматривает значения NaN как равные и превышающие все значения, отличные от NaN.

Типы decimal и numeric эквивалентны. Оба типа являются частью стандарта SQL.

При округлении значений числовой тип округляет связи от нуля в то время как (на большинстве машин) типы real и double precision округляют до ближайшего четного числа. Например:

SELECT x,
  round(x::numeric) AS num_round,
  round(x::double precision) AS dbl_round
FROM generate_series(-3.5, 3.5, 1) as x;
  x   | num_round | dbl_round
------+-----------+-----------
 -3.5 |        -4 |        -4
 -2.5 |        -3 |        -2
 -1.5 |        -2 |        -2
 -0.5 |        -1 |        -0
  0.5 |         1 |         0
  1.5 |         2 |         2
  2.5 |         3 |         2
  3.5 |         4 |         4
(8 rows)

Типы с плавающей точкой

Типы данных real и double precision являются неточными числовыми типами переменной точности. На всех поддерживаемых в настоящее время платформах эти типы являются реализациями стандарта IEEE 754 для двоичной арифметики с плавающей запятой (одинарной и двойной точности соответственно) в той степени, в которой это поддерживается базовым процессором, операционной системой и компилятором.

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

• Если вам требуется точное хранение и расчеты (например, для денежных сумм), используйте вместо этого числовой тип.

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

• Сравнение двух значений с плавающей точкой на равенство не всегда может работать как ожидалось.

На всех поддерживаемых в настоящее время платформах real тип имеет диапазон от 1E**^-37** до 1E**⁺³⁷** с точностью не менее 6 десятичных цифр. Тип double precision имеет диапазон от 1E**^-307** до 1E**⁺³⁰⁸** с точностью не менее 15 цифр. Значения, которые являются слишком большими или слишком маленькими, вызовут ошибку. Округление может иметь место, если точность введенного числа слишком высока. Числа, слишком близкие к нулю, которые не могут быть представлены отличными от нуля, вызовут ошибку недостаточного значения.

По умолчанию значения с плавающей запятой выводятся в текстовой форме в самом кратком десятичном представлении; полученное десятичное значение ближе к истинному сохраненному двоичному значению, чем к любому другому значению, представляемому с той же двоичной точностью. (Однако выходное значение в настоящее время никогда не бывает точно посередине между двумя представимыми значениями, чтобы избежать широко распространенной ошибки, когда входные подпрограммы не соблюдают должным образом правило округления до ближайшего четного). Это значение будет использовать не более 17 значащих десятичных цифр для значения float8 и не более 9 цифр для значений float4.

Заметка
Этот формат вывода с наименьшей точностью вывода генерируется намного быстрее, чем исторически сложившийся формат округления.

Для снижения точности вывода, можно использовать параметр extra_float_digits для выбора округления десятичного числа. Установка значения 0 восстанавливает предыдущее значение округления по умолчанию до 6 (для float4) или 15 (для float8) значащих десятичных цифр. Установка отрицательного значения уменьшает количество цифр; например, -2 округляет вывод до 4 или 13 цифр соответственно.

Любое значение extra_float_digits больше 0 выбирает формат с наименьшей точностью.

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

• Infinity

• -Infinity

• NaN

Они представляют специальные значения IEEE 754 «бесконечность», «отрицательная бесконечность» и «не число» соответственно. При записи этих значений в качестве констант в SQL-команде необходимо заключить их в кавычки, например, UPDATE table SET x = '-Infinity'. При вводе эти строки распознаются без учета регистра.

Заметка
IEEE754 указывает, что NaN не должен сравниваться с любым другим значением с плавающей запятой (включая NaN). Чтобы позволить значениям с плавающей точкой сортироваться и использоваться в древовидных индексах, QHB рассматривает значения NaN как равные и превышающие все значения, отличные от NaN.

QHB также поддерживает стандартные обозначения SQL float и float(p) для указания неточных (inexact) числовых типов. Здесь p указывает минимально допустимую точность в двоичных разрядах. QHB принимает значения от float(1) до float(24) как выбор типа real, в то время как значения от float(25) до float(53) как выбор типа double precision. Значения p вне допустимого диапазона формируют ошибку. float без заданной точности считается double precision.

Серийные типы

В этом разделе описывается специфичный для QHB способ создания столбца автоинкрементирования. Другой способ - использовать функцию столбца идентификаторов стандарта SQL, см. в описании CREATE TABLE.

Типы данных smallserial, serial и bigserial — это не настоящие типы, а просто удобство записи для создания столбцов уникальных идентификаторов (аналогично свойству AUTO_INCREMENT поддерживаемому некоторыми другими базами данных). В текущей реализации, указание:

CREATE TABLE tablename (
    colname SERIAL
);

эквивалентно указанию:

CREATE SEQUENCE tablename_colname_seq AS integer;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

Таким образом, создаётся целочисленный столбец и его значения по умолчанию организованы для назначения из генератора последовательности. Ограничение NOT NULL применяется, чтобы гарантировать, что нулевое значение не может быть вставлено. (В большинстве случаев вы также хотели бы присоединить ограничение UNIQUE или PRIMARY KEY чтобы предотвратить случайную вставку дублирующихся значений, но это не происходит автоматически). Наконец, последовательность помечается как «принадлежащая» столбцу, так что она будет удалена, если столбец или таблица будут удалены.

Заметка
Поскольку smallserial, serial и bigserial реализованы с использованием последовательностей, в последовательности значений, которая появляется в столбце, могут быть «дыры» или пробелы, даже если строки никогда не удаляются. Значение, выделенное из последовательности, все равно будет «израсходовано», даже если строка, содержащая это значение, никогда не будет успешно вставлена в столбец таблицы. Это может произойти, например, если транзакция вставки откатывается. Подробности смотрите в nextval() в разделе Функции управления последовательностями.

Чтобы вставить следующее значение последовательности в столбец последовательности, укажите, что столбцу последовательности должно быть присвоено его значение по умолчанию. Это можно сделать либо путем исключения столбца из списка столбцов в инструкции INSERT, либо с помощью ключевого слова DEFAULT.

Имена типов serial и serial4 эквивалентны: оба создают столбцы типа integer. Имена типов bigserial и serial8 работают аналогично, за исключением того, что они создают столбец bigint. Следует использовать bigserial, если вы предполагаете использовать более 2**³¹** идентификаторов за время существования таблицы. Имена типов smallserial и serial2 также работают аналогично, за исключением того, что они создают столбец smallint.

Последовательность, созданная для столбца последовательности, автоматически удаляется при удалении столбца-владельца. Вы можете удалить последовательность без удаления столбца, но это приведет к удалению выражения по умолчанию для столбца.

Денежные Типы

Тип money хранит сумму в валюте с фиксированной дробной точностью; см. таблицу 3. Дробная точность определяется настройкой базы данных lc_monetary. Диапазон, указанный в таблице, предполагает наличие двух дробных цифр. Ввод принимается в различных форматах, включая целочисленные литералы и литералы с плавающей запятой, а также типичное форматирование валюты, например, ’$1,000.00’. Вывод обычно в последней форме, но зависит от локали.

Таблица 3. Денежные Типы

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

Значения типов данных numeric, int и bigint могут быть приведены к money. Преобразование из типов данных real и double precision можно выполнить, если сначала привести к типу numeric, например:

SELECT '12.34'::float8::numeric::money;

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

Денежное значение может быть приведено к числовому без потери точности. Преобразование в другие типы потенциально может привести к потере точности и также должно выполняться в два этапа:

SELECT '52093.89'::money::numeric::float8;

Деление денежной величины на целое число производится с усечением дробной части в сторону нуля. Чтобы получить округленный результат, разделите на значение с плавающей запятой или преобразуйте денежное значение в числовое перед делением и обратно в денежное после этого. (Последнее предпочтительнее, чтобы избежать риска потери точности). Когда денежная стоимость делится на другую денежную стоимость, результат получается double precision (т. е. чистое число, а не деньги); денежные единицы отменяют друг друга при делении.

Символьные типы

Таблица 4. Символьные типы

Таблица 4 показывает типы символов общего назначения, доступные в QHB.

SQL определяет два основных типа символов: character varying(n) и character(n), где n - положительное целое число. Оба этих типа могут хранить строки длиной до n символов (не байтов). Попытка сохранить более длинную строку в столбце этих типов приведет к ошибке, если только избыточные символы не являются пробелами, в этом случае строка будет усечена до максимальной длины. (Это несколько странное исключение требуется стандартом SQL). Если строка, которая должна быть сохранена, короче объявленной длины, значения character будут дополнены пробелами; Значения типа character varying просто сохранят более короткую строку.

Если кто-либо явно преобразует значение в character varying(n) или character(n), то значение чрезмерной длины будет усечено до n символов без возникновения ошибки. (Это также требуется стандартом SQL).

Обозначения varchar(n) и char(n) являются псевдонимами для character varying(n) и character(n), соответственно. character без спецификатора длины эквивалентен character(1). Если character varying используется без спецификатора длины, тип принимает строки любого размера. Последнее является расширением QHB.

Кроме того, QHB предоставляет тип text, в котором хранятся строки любой длины. Хотя тип text не соответствует стандарту SQL, он есть и в некоторых других системах управления базами данных SQL.

Значения типа character физически дополняются пробелами до указанной ширины n и сохраняются и отображаются таким образом. Однако конечные пробелы обрабатываются как семантически несущественные и не учитываются при сравнении двух значений типа character. В сопоставлениях, где пробел является значительным, такое поведение может привести к неожиданным результатам; например, SELECT 'a '::CHAR(2) collate "C" < E'a\n'::CHAR(2) возвращает true, даже если в локали C пробел будет больше новой строки. Конечные пробелы удаляются при преобразовании character значения в один из других типов строк. Обратите внимание, что конечные пробелы семантически значимы в character varying и text значениях, а также при использовании сопоставления с шаблоном оператором LIKE и в регулярных выражениях.

Потребность в памяти для короткой строки (до 126 байт) составляет 1 байт плюс фактическая строка, которая включает заполнение пробелами в случае character. Более длинные строки имеют 4 байта служебной информации вместо 1. Длинные строки сжимаются системой автоматически, поэтому физические требования к диску могут быть меньше. Очень длинные значения также хранятся в фоновых таблицах, чтобы они не мешали быстрому доступу к более коротким значениям столбцов. В любом случае самая длинная строка символов, которую можно сохранить, составляет около 1 ГБ. (Максимальное значение, которое будет разрешено для n в объявлении типа данных, меньше этого. Менять это было бы бесполезно, поскольку в многобайтовых кодировках число символов и байтов может быть совершенно разным. Если вы хотите сохранить длинные строки без определенного верхнего предела, используйте text или character varying без спецификатора длины вместо того, чтобы создавать произвольный предел длины).

Заметка
Между этими тремя типами нет разницы в производительности, за исключением увеличения места для хранения при использовании типа с пробелом и нескольких дополнительных циклов ЦП для проверки длины при сохранении в столбце с ограниченной длиной. Хотя character(n) имеет преимущества в производительности в некоторых других системах баз данных, в QHB такого преимущества нет; на самом деле character(n) обычно самый медленный из трех из-за его дополнительных затрат на хранение. В большинстве случаев вместо этого следует использовать text или character varying.

Обратитесь к разделу Строковые константы за информацией о синтаксисе строковых литералов, а также к главе Функции и операторы за информацией о доступных операторах и функциях. Набор символов базы данных определяет набор символов, используемый для хранения текстовых значений; для получения дополнительной информации о поддержке набора символов обратитесь к разделу Поддержка набора символов.

Пример 7.1. Использование типов символов

CREATE TABLE test1 (a character(4));
INSERT INTO test1 VALUES ('ok');
SELECT a, char_length(a) FROM test1; -- (1)

  a   | char_length
------+-------------
 ok   |           2


CREATE TABLE test2 (b varchar(5));
INSERT INTO test2 VALUES ('ok');
INSERT INTO test2 VALUES ('good      ');
INSERT INTO test2 VALUES ('too long');
ERROR:  value too long for type character varying(5)
INSERT INTO test2 VALUES ('too long'::varchar(5)); -- explicit truncation
SELECT b, char_length(b) FROM test2;

   b   | char_length
-------+-------------
 ok    |           2
 good  |           5
 too l |           5

Заметка
Функция char_length обсуждается в разделе Строковые функции и операторы

В QHB есть два других символьных типа фиксированной длины, показанных в Таблице 5. Тип name существует только для хранения идентификаторов во внутренних системных каталогах и не предназначен для использования обычными пользователями. Его длина в настоящее время определяется как 64 байта (63 используемых символа плюс терминатор), но на него следует ссылаться, используя константу NAMEDATALEN в исходном коде C/RUST. Длина устанавливается во время компиляции (и, следовательно, настраивается для специальных целей); максимальная длина по умолчанию может измениться в будущем выпуске. Тип "char" (обратите внимание на кавычки) отличается от char(1) тем, что он использует только один байт памяти. Он используется внутри системных каталогов как упрощенный тип перечисления.

Таблица 5. Специальные типы символов

Двоичные типы данных

Тип данных bytea позволяет хранить двоичные строки; см. таблицу 7.6.

Таблица 6. Двоичные типы данных

Бинарная строка — это последовательность октетов (или байтов). Двоичные строки отличаются от символьных строк двумя способами. Во-первых, двоичные строки специально позволяют хранить октеты с нулевым значением и другие «непечатные» октеты (как правило, октеты вне десятичного диапазона от 32 до 126). Строки символов запрещают нулевые октеты, а также запрещают любые другие значения октетов и последовательности значений октетов, которые являются недопустимыми в соответствии с выбранной кодировкой набора символов базы данных. Во-вторых, операции над двоичными строками обрабатывают фактические байты, тогда как обработка символьных строк зависит от настроек локали. Короче говоря, двоичные строки подходят для хранения данных, которые программист считает «необработанными байтами», тогда как символьные строки подходят для хранения текста.

Тип bytea поддерживает два формата для ввода и вывода: формат «hex» (шестнадцатеричный) и исторический формат QHB «escape» (экранированный). Они оба всегда принимаются на вход. Формат вывода зависит от параметра конфигурации bytea_output; по умолчанию используется «hex».

Стандарт SQL определяет другой тип двоичной строки, который называется BLOB или BINARY LARGE OBJECT. Формат ввода отличается от bytea, но предоставляемые функции и операторы в основном одинаковы.

«Шестнадцатеричный» формат bytea

«Шестнадцатеричный» формат кодирует двоичные данные в виде 2 шестнадцатеричных цифр на байт, наиболее значимые из которых являются первыми. Всей строке предшествует последовательность \x (чтобы отличить ее от escape-формата). В некоторых контекстах первоначальный обратный слеш, возможно, должен быть экранирован путем его удвоения (см. раздел Строковые константы). Для ввода шестнадцатеричные цифры могут быть в верхнем или нижнем регистре, и пробел допускается между парами цифр (но не внутри пары цифр и не в начальной последовательности \x). Шестнадцатеричный формат совместим с широким спектром внешних приложений и протоколов, и он, как правило, быстрее преобразуется, чем escape-формат, поэтому его использование является предпочтительным.

Пример:

SELECT '\xDEADBEEF';

Экранированный формат bytea

Экранированный формат является традиционным форматом QHB для типа bytea. В нем используется подход, представляющий двоичную строку как последовательность символов ASCII, при этом преобразовываются те байты, которые не могут быть представлены в виде символа ASCII, в специальные escape-последовательности. Если с точки зрения приложения представление байтов в виде символов имеет смысл, то такое представление может быть удобным. Но на практике это обычно сбивает с толку, поскольку стирает различия между двоичными строками и символьными строками, а также конкретный выбранный управляющий механизм несколько громоздок. Следовательно, этого формата, вероятно, следует избегать для большинства новых приложений.

При вводе значений bytea в escape-формате, октеты определенных значений должны быть экранированы, тогда как все октетные значения могут быть экранированы. В общем случае, чтобы экранировать октет, преобразуйте его в трехзначное восьмеричное значение и поставьте перед ним обратную косую черту. Сама обратная косая черта (десятичное значение октета 92) альтернативно может быть представлена двойной обратной косой чертой. Таблица 7 показывает символы, которые должны быть экранированы, и дает альтернативные escape-последовательности, где это применимо.

Таблица 7. Экранированные символы октета bytea

Требование экранирования не печатаемых октетов зависит от настроек локали. В некоторых случаях вы можете оставить их без экранирования.

Причина, по которой одинарные кавычки должны быть удвоены, как показано в Таблице 7, заключается в том, что это верно для любого строкового литерала в команде SQL. Универсальный синтаксический анализатор строковых литералов использует самые крайние одинарные кавычки и сводит любую пару одинарных кавычек к одному символу данных. То, что функция ввода bytea видит, — это всего лишь одна одинарная кавычка, которую она обрабатывает как простой символ данных. Однако входная функция bytea обрабатывает обратную косую черту как спецсимвол, и другие действия, показанные в Таблице 7, реализуются этой функцией.

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

Октеты bytea выводятся в шестнадцатеричном формате по умолчанию. Если вы измените bytea_output на escape, «непечатные» октеты преобразуются в их эквивалентные трехзначные восьмеричные значения и им предшествует один обратный слеш. Большинство «печатаемых» октетов выводятся по их стандартному представлению в клиентском наборе символов, например:

SET bytea_output = 'escape';

SELECT 'abc \153\154\155 \052\251\124'::bytea;
     bytea
----------------
 abc klm *\251T

Октет с десятичным значением 92 (обратная косая черта) удваивается в выходных данных. Подробности в Таблице 8.

Таблица 8. Выходные экранированные октеты bytea

В зависимости от используемого интерфейса QHB, вам может потребоваться выполнить дополнительную работу, связанную с экранированием и деэкранированием строк bytea. Например, вам также может потребоваться экранировать перевод строки и возврат каретки, если ваш интерфейс автоматически их переводит.

Типы даты/времени

QHB поддерживает полный набор типов даты и времени SQL, показанных в Таблице 9. Операции, доступные для этих типов данных, описаны в разделе Функции и операторы даты/времени. Даты подсчитываются в соответствии с григорианским календарем, даже за годы до того, как этот календарь был введен .

Таблица 9. Типы даты / времени

Заметка
Стандарт SQL требует, чтобы запись только timestamp была эквивалентна timestamp without time zone, и QHB соблюдает это поведение. timestamptz принимается как сокращение от timestamp with time zone; это расширение QHB.

time, timestamp и interval принимают необязательное значение точности p которое указывает количество дробных цифр, сохраняемых в поле секунд. По умолчанию нет явного ограничения точности. Допустимый диапазон p составляет от 0 до 6.

Тип interval имеет дополнительную опцию, которая заключается в ограничении набора хранимых полей, путем записи одной из этих фраз:

YEAR
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR TO MONTH
DAY TO HOUR
DAY TO MINUTE
DAY TO SECOND
HOUR TO MINUTE
HOUR TO SECOND
MINUTE TO SECOND

Обратите внимание, если при выборе интервала, указаны оба поля fields и p, то fields должен содержать SECOND, поскольку точность применяется только к секундам.

Тип time with time zone определяется стандартом SQL, но определение обладает свойствами, которые приводят к сомнительной полезности. В большинстве случаев сочетание date, time, timestamp without time zone и timestamp with time zone должно обеспечивать полный диапазон функциональных возможностей даты/времени, требуемых для любого приложения.

Ввод даты / времени

Ввод даты и времени допускается практически в любом приемлемом формате, включая ISO 8601, SQL- совместимый, традиционный POSTGRES и другие. Для некоторых форматов порядок ввода даты, месяца и года в дате является неоднозначным, и существует поддержка для определения ожидаемого порядка этих полей. Установите для параметра DateStyle значение MDY чтобы выбрать интерпретацию месяц-день-год, DMY для выбора интерпретации день-месяц-год или YMD чтобы выбрать интерпретацию год-месяц-день.

Помните, что любые литералы даты или времени должны быть заключены в одинарные кавычки, как текстовые строки. Обратитесь в раздел Константы других типов за дополнительной информацией. SQL требует следующий синтаксис

type [ (p) ] 'value'

где p - необязательная спецификация точности, дающая количество дробных цифр в поле секунд. Точность может быть указана для типов time, timestamp и interval и может варьироваться от 0 до 6. Если точность не указана в спецификации константы, по умолчанию используется точность литерального значения (но не более 6 цифр).

Даты

Таблица 10 показывает некоторые возможные входные данные для типа даты.

Таблица 10. Ввод даты

Время

Типы времени суток -это time [ (p) ] without time zone и time [ (p) ] with time zone. «time» эквивалентно «time without time zone».

Допустимые входные данные для этих типов состоят из времени суток, за которым следует необязательный часовой пояс. (См. таблицу 11 и таблицу 12). Если часовой пояс указан во входных данных для time without time zone, он игнорируется. Вы также можете указать дату, но она будет игнорироваться, за исключением случаев, когда вы используете имя часового пояса, которое включает правило перехода на летнее время, например America/New_York. В этом случае указание даты требуется для определения того, применяется ли стандартное или летнее время. Соответствующее смещение часового пояса записывается в поле со значением time with time zone.

Таблица 11. Ввод времени

Таблица 12. Ввод часового пояса

Обратитесь к разделу Часовые пояса за дополнительной информацией о том, как указать часовые пояса.

Отметки времени

Допустимые входные данные для типов отметок времени состоят из объединения даты и времени, за которым следует необязательный часовой пояс, за которым следует необязательный AD или BC. (В качестве альтернативы AD/BC могут появляться перед часовым поясом, но это не предпочтительный порядок). Таким образом:

1999-01-08 04:05:06

и:

1999-01-08 04:05:06 -8:00

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

January 8 04:05:06 1999 PST

Стандарт SQL различает метку времени без часового пояса и метку времени с литералами часового пояса по наличию символа «+» или «-» и смещению часового пояса после времени. Следовательно, согласно стандарту,

TIMESTAMP '2004-10-19 10:23:54'

это метка времени без часового пояса, в то время как

TIMESTAMP '2004-10-19 10:23:54+02'

это метка времени со значением часового пояса. QHB никогда не проверяет содержимое литеральной строки перед определением ее типа, и поэтому будет обрабатывать оба вышеперечисленные выражения как метку времени без часового пояса. Чтобы литерал обрабатывался как метка времени со значением часового пояса, присвойте ему правильный явный тип:

TIMESTAMP WITH TIME ZONE '2004-10-19 10:23:54+02'

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

Для метки времени со значением часового пояса внутреннее сохраненное значение всегда указывается в формате UTC (универсальное координированное время, традиционно известное как среднее время по Гринвичу, GMT). Входное значение с указанным явным часовым поясом преобразуется в UTC с использованием соответствующего смещения для этого часового пояса. Если во входной строке не указан часовой пояс, предполагается, что он находится в часовом поясе, указанном системным параметром TimeZone, и преобразуется в UTC с использованием смещения для зоны часового пояса.

Когда выводится метка времени со значением часового пояса, она всегда преобразуется из UTC в текущую зону часового пояса и отображается как местное время в этой зоне. Чтобы увидеть время в другом часовом поясе, измените часовой пояс или используйте конструкцию AT TIME ZONE (см. раздел AT TIME ZONE).

Преобразования между меткой времени без часового пояса и меткой времени с часовым поясом обычно предполагают, что значение метки времени без часового пояса должно быть принято или задано как местное время часового пояса. Для преобразования можно указать другой часовой пояс, используя AT TIME ZONE.

Специальные значения

QHB для удобства поддерживает несколько специальных значений ввода даты/времени, как показано в Таблице 13. Значения infinity и -infinity специально представлены внутри системы и будут отображаться без изменений; другие же - просто сокращенные обозначения, которые при чтении будут преобразованы в обычные значения даты/времени. (В частности, now и связанные строки преобразуются в определенное значение времени, как только они будут прочитаны). Все эти значения должны быть заключены в одинарные кавычки при использовании в качестве констант в командах SQL.

Таблица 13. Специальные даты / времени

Следующие SQL- совместимые функции также можно использовать для получения текущего значения времени для соответствующего типа данных: CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCALTIME, LOCALTIMESTAMP. Последние четыре принимают необязательную спецификацию с точностью до секунды. (См. раздел Текущая дата/время). Обратите внимание, что они являются функциями SQL и не распознаются в строках ввода данных.

Формат вывода типов дата/время

Формат вывода типов даты / времени может быть установлен в один из четырех стилей ISO 8601, SQL (Ingres), традиционный POSTGRES (формат даты Unix) или немецкий. По умолчанию используется формат ISO. (Стандарт SQL требует использования формата ISO 8601. Название формата вывода «SQL» является исторической случайностью). В Таблице 14 приведены примеры каждого стиля вывода. Выходные данные типов date и time как правило, представляет собой только часть даты или времени в соответствии с приведенными примерами. Однако стиль POSTGRES выводит значения только для даты в формате ISO.

Таблица 14. Стили вывода даты / времени

Заметка
ISO 8601 определяет использование заглавной буквы T для разделения даты и времени. QHB принимает этот формат на входе, но на выходе он использует пробел, а не T, как показано выше. Это для удобочитаемости и для соответствия RFC 3339, а также некоторым другим системам баз данных.

В стилях SQL и POSTGRES день отображается перед месяцем, если указан порядок полей DMY, в противном случае месяц отображается перед днем. (См. раздел Формат вывода типов дата/время о том, как этот параметр также влияет на интерпретацию входных значений). В Таблице 15 приведены примеры.

Таблица 15. Соглашение о дате заказа

Стиль даты/времени может быть выбран пользователем с помощью команды SET datestyle, параметра DateStyle в файле конфигурации qhb.conf или используя переменную среды PGDATESTYLE на сервере или клиенте.

Функция форматирования to_char (см. раздел Функции форматирования типов данных) также доступна как более гибкий способ форматирования данных даты/времени.

Часовые пояса

Часовые пояса и условности часовых поясов зависят от политических решений, а не только от геометрии Земли. В 1900-х годах часовые пояса во всем мире стали несколько стандартизированы, но по-прежнему подвержены произвольным изменениям, особенно в отношении правил перехода на летнее время. QHB использует широко используемую базу данных часовых поясов IANA (Olson) для получения информации о исторических правилах часовых поясов. Что касается времени в будущем, предполагается, что последние известные правила для данного часового пояса будут продолжать соблюдаться в течение неопределенного времени в будущем.

QHB стремится быть совместимым со стандартными определениями SQL для типичного использования. Однако стандарт SQL имеет странное сочетание типов и возможностей даты и времени. Есть две очевидные проблемы:

• Хотя тип date не может иметь связанный часовой пояс, тип time может. Часовые пояса в реальном мире не имеют большого значения, если они не связаны ни с датой, ни с временем, поскольку смещение может изменяться в течение года с переходом на летнее время.

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

Чтобы устранить эти трудности, мы рекомендуем использовать типы даты/времени, которые содержат дату и время при использовании часовых поясов. Мы не рекомендуем использовать тип time with time zone (хотя он поддерживается QHB для соответствия стандарту SQL). QHB предполагает ваш местный часовой пояс для любого типа, содержащего только дату или время.

Все даты и время с учетом часового пояса хранятся внутри в формате UTC. Они преобразуются в местное время в зоне, указанной параметром конфигурации TimeZone перед отображением клиенту.

QHB позволяет указывать часовые пояса в трех разных формах:

• Полное название часового пояса, например, America/New_York. Распознанные имена часовых поясов перечислены в представлении pg_timezone_names (см. pg_timezone_names). Для этой цели QHB использует широко используемые данные часового пояса IANA, поэтому те же имена часовых поясов также распознаются другим программным обеспечением.

• Сокращение часового пояса, например PST. Такая спецификация просто определяет конкретное смещение от UTC, в отличие от полных имен часовых поясов, которые также могут подразумевать набор правил перехода на летнее время. Распознанные сокращения перечислены в представлении pg_timezone_abbrevs (см. раздел pg_timezone_abbrevs). Вы не можете задать параметры конфигурации TimeZone или log_timezone для сокращения часового пояса, но вы можете использовать сокращения во входных значениях даты/времени и с оператором AT TIME ZONE.

• В дополнение к названиям и аббревиатурам часовых поясов QHB будет принимать спецификации часовых поясов в стиле POSIX в виде STDoffset или STDoffsetDST, где STD - сокращение зоны, offset - числовое смещение в часах к западу от UTC, а DST - это необязательное сокращение зоны перехода на летнее время, предполагаемое на один час впереди заданного смещения. Например, если EST5EDT еще не является распознанным названием зоны, оно будет принято и будет функционально эквивалентно времени Восточного побережья США. В этом синтаксисе сокращение зоны может быть строкой букв или произвольной строкой, заключенной в угловые скобки (<>). Когда присутствует сокращение зоны перехода на летнее время, предполагается, что оно будет использоваться в соответствии с теми же правилами перехода на летнее время, которые используются в записи posixrules базы данных часовых поясов IANA. В стандартной установке QHB posixrules совпадает с US/Eastern, поэтому спецификации часовых поясов в стиле POSIX соответствуют правилам перехода на летнее время в США. При необходимости вы можете изменить это поведение, заменив файл posixrules.

Короче говоря, в этом разница между сокращениями и полными именами: сокращения представляют конкретное смещение от UTC, тогда как многие полные имена подразумевают локальное правило перехода на летнее время и поэтому имеют два возможных смещения UTC. Например, 2014-06-04 12:00 America/New_York представляет полуденное местное время в Нью-Йорке, которое для этой конкретной даты было восточным летним временем (UTC-4). Итак, 2014-06-04 12:00 EDT указывает тот же момент времени. Но 2014-06-04 12:00 EST указывает полдень по восточному поясному времени (UTC-5), независимо от того, было ли летнее время номинально действующим на эту дату.

Чтобы усложнить ситуацию, некоторые юрисдикции использовали одно и то же сокращение часового пояса для обозначения разных смещений UTC в разное время; например, в Москве MSK означало UTC + 3 в некоторые годы и UTC + 4 в другие. QHB интерпретирует такие сокращения в соответствии с тем, что они имели в виду (или имели в виду совсем недавно) в указанную дату; но, как и в приведенном выше примере EST, это не обязательно совпадает с местным гражданским временем этой даты.

Следует опасаться, что функция часового пояса в стиле POSIX может привести к молчаливому принятию фиктивного ввода, поскольку нет никакой проверки на правильность сокращений зон. Например, SET TIMEZONE TO FOOBAR0 будет работать, оставляя систему эффективно использующей довольно своеобразное сокращение для UTC. Другая проблема, о которой следует помнить, заключается в том, что в именах часовых поясов POSIX положительные смещения используются для местоположений к западу от Гринвича. В любом другом месте QHB следует соглашению ISO-8601, согласно которому положительные смещения часовых поясов находятся к востоку от Гринвича.

Во всех случаях названия и сокращения часовых поясов распознаются без учета регистра.

Ни имена часовых поясов, ни сокращения не встроены в сервер; они получены из файлов конфигурации, хранящихся в папках .../share/timezone/ и .../share/timezonesets/ установочного каталога .

Параметр конфигурации TimeZone можно установить в файле qhb.conf или любым другим стандартным способом, описанным в главе Конфигурация сервера. Есть также несколько специальных способов установить его:

• SQL-команда SET TIME ZONE устанавливает часовой пояс для сеанса. Это альтернативное написание команды SET TIMEZONE TO с синтаксисом более совместимым со спецификацией SQL.

• Переменная окружения PGTZ используется клиентами libpq для отправки команды SET TIME ZONE на сервер при подключении.

Формат ввода интервалов

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

[@] quantity unit [quantity unit...] [direction]

где quantity (количество) — это число (возможно, подписанное); unit (единица измерения) - microsecond, millisecond, second, minute, hour, day, week, month, year, decade, century, millennium или сокращения или множественные числа этих единиц; direction (направление) может быть ago (назад) или пустым. Знак (@) является необязательным. Суммы различных единиц неявно суммируются с помощью соответствующего знака учета. ago меняет знак всех полей. Этот синтаксис также используется для вывода интервала, если для IntervalStyle установлено значение postgres_verbose.

Количество дней, часов, минут и секунд может быть указано без явной маркировки единиц измерения. Например, ’1 12:59:10’ читается так же, как ’1 day 12 hours 59 min 10 sec’. Кроме того, комбинация лет и месяцев может быть указана с тире; например, ’200-10’ читается так же, как "200 years 10 months". (Эти более короткие формы фактически являются единственными, разрешенными стандартом SQL, и используются для вывода, когда для IntervalStyle установлено значение sql_standard).

Значения интервалов также можно записать в виде временных интервалов ISO 8601, используя либо «формат с обозначениями» раздела 4.4.3.2 стандарта, либо «альтернативный формат» раздела 4.4.3.3. Формат с обозначениями выглядит так:

P quantity unit [ quantity unit ...] [ T [ quantity unit ...]]

Строка должна начинаться с буквы P и может содержать букву T которая вводит единицы времени дня. Доступные сокращения единиц приведены в Таблице 16. Единицы могут быть опущены и могут быть указаны в любом порядке, но после T должны появляться единицы меньше, чем день. В частности, значение M зависит от того, находится ли он до или после T

Таблица 16. Сокращения единиц интервала ISO 8601

В альтернативном формате:

P [ years-months-days ] [ T hours:minutes:seconds ]

строка должна начинаться с P, а T разделяет части интервала даты и времени. Значения приведены в виде чисел, аналогичных датам ISO 8601.

При записи интервальной константы со спецификацией полей или при назначении строки интервальному столбцу, определенному спецификацией полей, интерпретация немаркированных величин зависит от полей. Например, INTERVAL '1' YEAR читается как 1 год, тогда как INTERVAL '1' означает 1 секунду. Кроме того, значения полей «справа» от наименее значимого поля, разрешенного спецификацией полей, молча отбрасываются. Например, запись INTERVAL '1 day 2:03:04' HOUR TO MINUTE приводит к удалению поля секунд, но не поля дня.

Согласно стандарту SQL все поля интервального значения должны иметь одинаковый знак, поэтому ведущий отрицательный знак применяется ко всем полям; например, знак минус в интервальном литерале ’-1 2:03:04’ применяется как к дням, так и к часам/минутам/секундам. QHB позволяет полям иметь разные знаки и традиционно обрабатывает каждое поле в текстовом представлении как независимо подписанное, так что часть часа/минуты/секунды считается положительной в этом примере. Если для IntervalStyle установлено значение sql_standard то ведущий знак считается применимым ко всем полям (но только если дополнительные знаки не появляются). В противном случае используется традиционная интерпретация QHB. Чтобы избежать неоднозначности, рекомендуется прикреплять явный знак к каждому полю, если какое-либо поле является отрицательным.

В подробном формате ввода и в некоторых полях более компактных форматов ввода значения полей могут иметь дробные части; например, ’1.5 week’ или ’01:02:03.45’. Такой ввод преобразуется в соответствующее количество месяцев, дней и секунд для хранения. Когда это приводит к дробному числу месяцев или дней, дробь добавляется в поля нижнего порядка с использованием коэффициентов преобразования 1 месяц = 30 дней и 1 день = 24 часа. Например, ’1.5 month’ становится 1 месяц и 15 дней. Только секунды будут отображаться как дробные на выходе.

В Таблице 17 приведены некоторые примеры правильных interval ввода.

Таблица 17. Интервальный ввод

Внутренние интервальные значения хранятся в виде месяцев, дней и секунд. Это сделано потому, что число дней в месяце варьируется, и день может иметь 23 или 25 часов, если требуется корректировка перехода на летнее время. Поля месяца и дня являются целыми числами, а поле секунд может хранить дроби. Поскольку интервалы обычно создаются из константных строк или вычитания меток времени, этот метод хранения в большинстве случаев работает хорошо, но может привести к неожиданным результатам:

SELECT EXTRACT(hours from '80 minutes'::interval);
 date_part
-----------
         1

SELECT EXTRACT(days from '80 hours'::interval);
 date_part
-----------
         0

Функции justify_days и justify_hours доступны для настройки дней и часов, которые выходят за пределы их нормальных диапазонов.

Формат вывода интервалов

Формат вывода типа интервала может быть установлен в один из четырех стилей sql_standard, postgres, postgres_verbose или iso_8601 с помощью команды SET intervalstyle. По умолчанию используется формат postgres. В Таблице 18 приведены примеры каждого стиля вывода.

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

Вывод стиля postgres совпадает с выводом выпусков PostgreSQL до 8.4, когда для параметра DateStyle было установлено значение ISO.

Вывод стиля postgres_verbose совпадает с выводом выпусков PostgreSQL до 8.4, когда для параметра DateStyle был установлен в значение, отличное от ISO.

Вывод стиля iso_8601 соответствует «формату с указателями», описанному в разделе 4.4.3.2 стандарта ISO 8601.

Таблица 18. Примеры стиля вывода интервала

Логический тип

QHB предоставляет стандартный логический (boolean) тип SQL; см. таблицу 19. Логический тип может иметь несколько состояний: «истина» (TRUE), «ложь» (FALSE) и третье состояние «неизвестно», которое представлено NULL значением SQL.

Таблица 19. Логический тип данных

Булевы константы могут быть представлены в запросах SQL ключевыми словами SQL TRUE, FALSE и NULL.

Функция ввода типа данных для логического типа принимает эти строковые представления для «истинного» состояния:

true
yes
on
1

и эти представления для «ложного» состояния:

false
no
off
0

Также допускаются уникальные префиксы этих строк, например, t или n. Начальные или конечные пробелы игнорируются, и регистр не имеет значения.

Функция вывода типа данных для логического типа всегда выдает либо t либо f, как показано в примере 7.2.

Пример 7.2. Использование логического типа

CREATE TABLE test1 (a boolean, b text);
INSERT INTO test1 VALUES (TRUE, 'sic est');
INSERT INTO test1 VALUES (FALSE, 'non est');
SELECT * FROM test1;
 a |    b
---+---------
 t | sic est
 f | non est

SELECT * FROM test1 WHERE a;
 a |    b
---+---------
 t | sic est

Ключевые слова TRUE и FALSE являются предпочтительным (SQL- совместимым) методом для записи логических констант в запросах SQL. Но вы также можете использовать строковые представления, следуя общему синтаксису строковой литеральной константы, описанному в раздел Константы других типов, например, ’yes’::boolean.

Обратите внимание, что синтаксический анализатор автоматически понимает, что TRUE и FALSE имеют логический тип, но это не так для NULL, потому что он может иметь любой тип. Поэтому в некоторых контекстах вам, возможно, придется явным образом привести NULL к логическому значению, например NULL::boolean. И наоборот, приведение может быть опущено из строково-литерального логического значения в контекстах, где анализатор может сделать вывод, что литерал должен иметь логический тип.

Перечисляемые типы

Перечислимые (enum) типы — это типы данных, которые содержат статический упорядоченный набор значений. Они эквивалентны перечислимым типам поддерживаемым во многих языках программирования. Примером перечисляемого типа могут быть дни недели или набор значений состояния для фрагмента данных.

Объявление перечислимых типов

Типы перечислений создаются с помощью команды CREATE TYPE, например:

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');

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

CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
CREATE TABLE person (
    name text,
    current_mood mood
);
INSERT INTO person VALUES ('Moe', 'happy');
SELECT * FROM person WHERE current_mood = 'happy';
 name | current_mood
------+--------------
 Moe  | happy
(1 row)

Порядок

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

INSERT INTO person VALUES ('Larry', 'sad');
INSERT INTO person VALUES ('Curly', 'ok');
SELECT * FROM person WHERE current_mood > 'sad';
 name  | current_mood
-------+--------------
 Moe   | happy
 Curly | ok
(2 rows)

SELECT * FROM person WHERE current_mood > 'sad' ORDER BY current_mood;
 name  | current_mood
-------+--------------
 Curly | ok
 Moe   | happy
(2 rows)

SELECT name
FROM person
WHERE current_mood = (SELECT MIN(current_mood) FROM person);
 name  
-------
 Larry
(1 row)

Надежность типа

Каждый перечисляемый тип данных является отдельным и не может сравниваться с другими перечисляемыми типами. Смотрите этот пример:

CREATE TYPE happiness AS ENUM ('happy', 'very happy', 'ecstatic');
CREATE TABLE holidays (
    num_weeks integer,
    happiness happiness
);
INSERT INTO holidays(num_weeks,happiness) VALUES (4, 'happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (6, 'very happy');
INSERT INTO holidays(num_weeks,happiness) VALUES (8, 'ecstatic');
INSERT INTO holidays(num_weeks,happiness) VALUES (2, 'sad');
ERROR:  invalid input value for enum happiness: "sad"
SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood = holidays.happiness;
ERROR:  operator does not exist: mood = happiness

Если вам действительно нужно сделать что-то подобное, вы можете написать собственный оператор или добавить явные приведения к вашему запросу:

SELECT person.name, holidays.num_weeks FROM person, holidays
  WHERE person.current_mood::text = holidays.happiness::text;
 name | num_weeks
------+-----------
 Moe  |         4
(1 row)

Детали реализации

Метки перечисления чувствительны к регистру, поэтому ’happy’ — это не то же самое, что ’HAPPY’. Пробелы в метках тоже значимы.

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

Значение перечисляемого типа занимает четыре байта на диске. Длина текстовой метки значения перечисления ограничена настройкой NAMEDATALEN скомпилированной в QHB; в стандартных сборках это означает максимум 63 байта.

Переводы из внутренних значений перечисления в текстовые метки хранятся в системном каталоге pg_enum. Запросы к этому каталогу напрямую могут быть полезны.

Геометрические типы

Геометрические типы данных представляют собой двумерные пространственные объекты. Таблица 20 показывает геометрические типы, доступные в QHB.

Таблица 20. Геометрические типы

Богатый набор функций и операторов доступен для выполнения различных геометрических операций, таких как масштабирование, перемещение, вращение и определение пересечений. Они объяснены в раздел Геометрические функции и операторы.

Точки

Точки являются фундаментальным двумерным строительным блоком для геометрических типов. Значения типа точка указываются с использованием одного из следующих синтаксисов:

( x, y )
  x, y

где x и y - соответствующие координаты в виде чисел с плавающей точкой.

Точки выводятся с использованием первого синтаксиса.

Линии

Линии представлены линейным уравнением Ax + By + C = 0, где A и B не равны нулю одновременно. Значения типа line вводятся и выводятся в следующем виде:

{ A, B, C }

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

[ ( x1, y1 ), ( x2, y2 ) ]
( ( x1, y1 ), ( x2, y2 ) )
  ( x1, y1 ), ( x2, y2 )
    x1, y1  ,   x2, y2

где (x1, y1) и (x2, y2) две разные точки на линии.

Отрезки линии

Отрезки линии представлены парами точек, которые являются конечными точками отрезка. Значения типа lseg указываются с использованием любого из следующих синтаксисов:

[ ( x1, y1 ), ( x2, y2 ) ]
( ( x1, y1 ), ( x2, y2 ) )
  ( x1, y1 ), ( x2, y2 )
    x1, y1  ,   x2, y2

где (x1, y1) и (x2, y2) - конечные точки отрезка линии.

Отрезки линии выводятся с использованием первого синтаксиса.

Рамки (boxes)

Рамки представлены парами точек, которые находятся в противоположных углах рамки. Значения типа box указываются с использованием любого из следующих синтаксисов:

( ( x1, y1 ), ( x2, y2 ) )
  ( x1, y1 ), ( x2, y2 )
    x1, y1  ,   x2, y2

где (x1, y1) и (x2, y2) - любые два противоположных угла рамки.

Рамки выводятся с использованием второго синтаксиса.

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

Пути

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

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

[ ( x1, y1 ), ..., ( xn, yn ) ]
( ( x1, y1 ), ..., ( xn, yn ) )
  ( x1, y1 ), ..., ( xn, yn )
  ( x1, y1  , ...,   xn, yn )
    x1, y1  , ...,   xn, yn

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

Пути выводятся с использованием первого или второго синтаксиса, в зависимости от ситуации.

Полигоны

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

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

( ( x1, y1 ), ..., ( xn, yn ) )
  ( x1, y1 ), ..., ( xn, yn )
  ( x1, y1  , ...,   xn, yn )
    x1, y1  , ...,   xn, yn

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

Полигоны выводятся с использованием первого синтаксиса.

Круги

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

< ( x, y ), r >
( ( x, y ), r )
  ( x, y ), r
    x, y  , r

где (x, y) - центральная точка, а r - радиус окружности.

Круги выводятся с использованием первого синтаксиса.

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

QHB предлагает типы данных для хранения адресов IPv4, IPv6 и MAC, как показано в Таблице 21. Лучше использовать эти типы вместо обычных текстовых типов для хранения сетевых адресов, потому что эти типы предлагают проверку ошибок ввода, а так же специализированные операторы и функции (см. раздел Функции и операторы сетевых адресов).

Таблица 21. Типы сетевых адресов

При сортировке типов данных inet или cidr адреса IPv4 всегда будут сортироваться до адресов IPv6, включая адреса IPv4, инкапсулированные или сопоставленные с адресами IPv6, например ::10.2.3.4 или ::ffff:10.4.3.2.

inet

Тип inet содержит адрес хоста IPv4 или IPv6 и, возможно, его подсеть, все в одном поле. Подсеть представлена количеством битов сетевого адреса, присутствующих в адресе хоста («маска сети»). Если маска сети равна 32, а адрес - IPv4, то это значение не указывает на подсеть, только на один хост. В IPv6 длина адреса составляет 128 бит, поэтому 128 бит указывают уникальный адрес хоста. Обратите внимание, что, если вы хотите принимать только сети, вы должны использовать тип cidr а не inet.

Формат ввода для этого типа: address/y где address - это адрес IPv4 или IPv6, а y - количество бит в маске сети. Если часть /y отсутствует, маска сети равна 32 для IPv4 и 128 для IPv6, поэтому значение представляет только один хост. На дисплее часть /y подавляется, если маска сети указывает один хост.

cidr

Тип cidr содержит спецификацию сети IPv4 или IPv6. Форматы ввода и вывода соответствуют правилам маршрутизации бесклассового интернет-домена. Форматом для указания сетей является address/y где address - это сеть, представленная в виде адреса IPv4 или IPv6, а y - количество битов в маске сети. Если y опущен, он рассчитывается с использованием допущений из старой классовой системы нумерации сети, за исключением того, что он будет по крайней мере достаточно большим, чтобы включать все октеты, записанные во входных данных. Указывать сетевой адрес, биты которого установлены справа от указанной маски, является ошибкой.

Таблица 22 показывает несколько примеров.

Таблица 22. Примеры ввода типа cidr

inet и cidr

Существенное различие между типами данных inet и cidr заключается в том, что inet принимает значения с ненулевыми битами справа от маски сети, а cidr - нет. Например, 192.168.0.1/24 действителен для inet но не для cidr.

Если вас не устраивает формат вывода inet или cidr, попробуйте функции host, text и abbrev.

macaddr

Тип macaddr хранит MAC-адреса, известные, например, по аппаратным адресам карты Ethernet (хотя MAC-адреса используются и для других целей). Ввод принимается в следующих форматах:

'08:00:2b:01:02:03'

'08-00-2b-01-02-03'

'08002b:010203'

'08002b-010203'

'0800.2b01.0203'

'0800-2b01-0203'

'08002b010203'

Все эти примеры будут указывать один и тот же адрес. Верхний и нижний регистр допускается для цифр от a до f. Вывод всегда в первой из показанных форм.

IEEE Std 802-2001 определяет вторую показанную форму (с дефисами) в качестве канонической формы для MAC-адресов и определяет первую форму (с двоеточиями) в качестве обратной битовой нотации, так что 08-00-2b-01-02-03 = 01:00:4D:08:04:0C. В настоящее время это соглашение широко игнорируется и относится только к устаревшим сетевым протоколам (таким как Token Ring). QHB не содержит положений об обращении битов, и все принятые форматы используют канонический порядок LSB.

Остальные пять форматов ввода не являются частью какого-либо стандарта.

macaddr8

Тип macaddr8 хранит MAC-адреса в формате EUI-64, известном, например, по аппаратным адресам платы Ethernet (хотя MAC-адреса также используются и для других целей). Этот тип может принимать MAC-адреса длиной 6 и 8 байтов и сохранять их в формате длины 8 байтов. MAC-адреса, предоставленные в 6-байтовом формате, будут сохраняться в 8-байтовом формате с 4-м и 5-м байтами, установленными в FF и FE соответственно. Обратите внимание, что IPv6 использует модифицированный формат EUI-64, где 7-й бит должен быть установлен в единицу после преобразования из EUI-48. Для этого изменения предусмотрена функция macaddr8_set7bit. Вообще говоря, доступен любой ввод, который состоит из пар шестнадцатеричных цифр (на границах байтов), необязательно последовательно разделяемых одним из ':', '-' или '.'. Количество шестнадцатеричных цифр должно быть 16 (8 байт) или 12 (6 байт). Начальные и конечные пробелы игнорируются. Ниже приведены примеры допустимых форматов ввода:

'08:00:2b:01:02:03:04:05'

'08-00-2b-01-02-03-04-05'

'08002b:0102030405'

'08002b-0102030405'

'0800.2b01.0203.0405'

'0800-2b01-0203-0405'

'08002b01:02030405'

'08002b0102030405'

Все эти примеры будут указывать один и тот же адрес. Верхний и нижний регистр допускается для цифр от a до f. Вывод всегда в первой из показанных форм. Последние шесть форматов ввода, которые упомянуты выше, не являются частью какого-либо стандарта. Чтобы преобразовать традиционный 48-битный MAC-адрес в формате EUI-48 в модифицированный формат EUI-64, который будет включен в качестве части хоста адреса IPv6, используйте macaddr8_set7bit как показано ниже:

SELECT macaddr8_set7bit('08:00:2b:01:02:03');

    macaddr8_set7bit     
-------------------------
 0a:00:2b:ff:fe:01:02:03
(1 row)

Типы битовых строк

Битовые строки — это строки 1 и 0. Их можно использовать для хранения или визуализации битовых масок. Существует два типа битовых строк SQL: bit(n) и bit varying(n), где n - положительное целое число.

Данные типа bit должны точно соответствовать длине n; попытка сохранить более короткие или более длинные битовые строки является ошибкой. bit varying данные имеют переменную длину вплоть до максимальной длины n; более длинные строки будут отклонены. Запись bit без длины эквивалентна bit(1), тогда как bit varying без указания длины означает неограниченную длину.

Заметка
Если явно привести значение битовой строки в bit(n), оно будет усечено или дополнено нулями справа, чтобы быть равно точно n битами, без возникновения ошибки. Аналогично, если явно привести значение битовой строки в bit varying(n), оно будет усечено справа, если оно больше, чем n бит.

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

Пример 7.3. Использование типов битовых строк

CREATE TABLE test (a BIT(3), b BIT VARYING(5));
INSERT INTO test VALUES (B'101', B'00');
INSERT INTO test VALUES (B'10', B'101');

ERROR:  bit string length 2 does not match type bit(3)

INSERT INTO test VALUES (B'10'::bit(3), B'101');
SELECT * FROM test;

  a  |  b
-----+-----
 101 | 00
 100 | 101

Значение битовой строки требует 1 байт для каждой группы из 8 битов, плюс 5 или 8 байтов служебной информации в зависимости от длины строки (но длинные значения могут быть сжаты или перемещены вне строки, как объяснено в разделе Символьные типы для символьных строк).

Типы текстового поиска

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

tsvector

Значение tsvector — это упорядоченный список различных лексем, которые представляют собой слова, которые были нормализованы для объединения различных вариантов одного и того же слова . Сортировка и удаление дубликатов выполняются автоматически во время ввода, как показано в этом примере:

SELECT 'a fat cat sat on a mat and ate a fat rat'::tsvector;
                      tsvector
----------------------------------------------------
 'a' 'and' 'ate' 'cat' 'fat' 'mat' 'on' 'rat' 'sat'

Чтобы представить лексемы, содержащие пробелы или знаки пунктуации, заключите их в кавычки:

SELECT $$the lexeme '    ' contains spaces$$::tsvector;
                 tsvector                  
-------------------------------------------
 '    ' 'contains' 'lexeme' 'spaces' 'the'

(Мы используем строковые литералы, заключенные в кавычки в этом и следующем примере, чтобы избежать путаницы с двойными кавычками внутри литералов). Встроенные кавычки и обратные слэши должны быть удвоены:

SELECT $$the lexeme 'Joe''s' contains a quote$$::tsvector;
                    tsvector                    
------------------------------------------------
 'Joe''s' 'a' 'contains' 'lexeme' 'quote' 'the'

По желанию, целочисленные позиции могут быть прикреплены к лексемам:

SELECT 'a:1 fat:2 cat:3 sat:4 on:5 a:6 mat:7 and:8 ate:9 a:10 fat:11 rat:12'::tsvector;
                                  tsvector
-------------------------------------------------------------------------------
 'a':1,6,10 'and':8 'ate':9 'cat':3 'fat':2,11 'mat':7 'on':5 'rat':12 'sat':4

Позиция обычно указывает местоположение исходного слова в документе. Позиционная информация может использоваться для ранжирования близости. Значения положения могут варьироваться от 1 до 16383; большие числа автоматически устанавливаются на 16383. Дублирующие позиции для одной и той же лексемы отбрасываются.

Лексемы, имеющие позиции, могут быть дополнительно помечены весом, который может быть A, B, C или D. D является значением по умолчанию и, следовательно, не отображается на выходе:

SELECT 'a:1A fat:2B,4C cat:5D'::tsvector;
          tsvector          
----------------------------
 'a':1A 'cat':5 'fat':2B,4C

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

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

SELECT 'The Fat Rats'::tsvector;
      tsvector      
--------------------
 'Fat' 'Rats' 'The'

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

SELECT to_tsvector('english', 'The Fat Rats');
   to_tsvector   
-----------------
 'fat':2 'rat':3

tsquery

Значение tsquery хранит лексемы, которые нужно искать, и может комбинировать их, используя логические операторы & (AND), | (OR) и ! (NOT), а также оператор поиска фразы <->; (FOLLOWED BY). Существует также вариант <N>; оператора FOLLOWED BY, где N - целочисленная константа, которая определяет расстояние между двумя искомыми лексемами. <-> эквивалентно <1>;.

Скобки могут быть использованы для принудительной группировки этих операторов. При отсутствии скобок, ! (NOT) связывается наиболее плотно, затем <->; (FOLLOWED BY) следующий наиболее плотно, затем & (AND), с | (OR) связываются наименее плотно.

Вот некоторые примеры:

SELECT 'fat & rat'::tsquery;
    tsquery    
---------------
 'fat' & 'rat'

SELECT 'fat & (rat | cat)'::tsquery;
          tsquery          
---------------------------
 'fat' & ( 'rat' | 'cat' )

SELECT 'fat & rat & ! cat'::tsquery;
        tsquery         
------------------------
 'fat' & 'rat' & !'cat'

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

SELECT 'fat:ab & cat'::tsquery;
    tsquery
------------------
 'fat':AB & 'cat'

Кроме того, лексемы в tsquery могут быть помечены * для указания соответствия префикса:

SELECT 'super:*'::tsquery;
  tsquery  
-----------
 'super':*

Этот запрос будет соответствовать любому слову в tsvector который начинается с «super».

Правила цитирования для лексем такие же, как описано ранее для лексем в tsvector; и, как и в случае с tsvector, любая необходимая нормализация слов должна быть выполнена перед преобразованием в тип tsquery. Функция to_tsquery удобна для выполнения такой нормализации:

SELECT to_tsquery('Fat:ab & Cats');
    to_tsquery    
------------------
 'fat':AB & 'cat'

Обратите внимание, что to_tsquery будет обрабатывать префиксы так же, как и другие слова, что означает, что это сравнение возвращает true:

SELECT to_tsvector( 'postgraduate' ) @@ to_tsquery( 'postgres:*' );
 ?column?
----------
 t

потому что postgres будет связан с postgr:

SELECT to_tsvector( 'postgraduate' ), to_tsquery( 'postgres:*' );
  to_tsvector  | to_tsquery
---------------+------------
 'postgradu':1 | 'postgr':*

который будет соответствовать основной форме postgraduate.

Тип UUID

Тип данных uuid хранит универсальные уникальные идентификаторы (UUID) в соответствии с RFC 4122, ISO / IEC 9834-8: 2005 и соответствующими стандартами. (Некоторые системы называют этот тип данных глобально уникальным идентификатором или GUID, вместо этого). Этот идентификатор является 128-битной величиной, которая генерируется алгоритмом, выбранным для того, чтобы сделать очень маловероятным, что этот же идентификатор будет сгенерирован кем-либо еще в известной вселенной с использованием того же алгоритма. Поэтому для распределенных систем эти идентификаторы обеспечивают лучшую гарантию уникальности, чем генераторы последовательностей, которые уникальны только в одной базе данных.

UUID записывается в виде последовательности шестнадцатеричных цифр в нижнем регистре, в нескольких группах, разделенных дефисами, в частности, в группе из 8 цифр, за которой следуют три группы из 4 цифр, за которыми следует группа из 12 цифр, всего 32 цифры, представляющие 128 бит Пример UUID в этой стандартной форме:

a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

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

A0EEBC99-9C0B-4EF8-BB6D-6BB9BD380A11
{a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11}
a0eebc999c0b4ef8bb6d6bb9bd380a11
a0ee-bc99-9c0b-4ef8-bb6d-6bb9-bd38-0a11
{a0eebc99-9c0b4ef8-bb6d6bb9-bd380a11}

Вывод всегда в стандартной форме.

QHB предоставляет функции хранения и сравнения для UUID, но основная база данных не содержит никакой функции для генерации UUID, потому что ни один алгоритм не подходит для каждого приложения. Модуль uuid-ossp предоставляет функции, которые реализуют несколько стандартных алгоритмов. Модуль pgcrypto также предоставляет функцию генерации случайных UUID. В качестве альтернативы UUID могут генерироваться клиентскими приложениями или другими библиотеками, вызываемыми через функцию на стороне сервера.

Тип XML

Тип данных xml может использоваться для хранения данных XML. Его преимущество перед хранением данных XML в текстовом поле состоит в том, что он проверяет входные значения на предмет корректности, и существуют вспомогательные функции для выполнения над ним безопасных операций; см. раздел Функции XML. Использование этого типа данных требует, чтобы установка была построена с помощью configure --with-libxml.

Тип xml может хранить правильно сформированные «документы», как определено стандартом XML, а также фрагменты «содержимого», которые определяются посредством ссылки на более разрешительный «document node» модели данных XQuery и XPath. Грубо говоря, это означает, что фрагменты контента могут иметь более одного элемента верхнего уровня или символьного узла. Выражение xmlvalue IS DOCUMENT можно использовать для оценки того, является ли конкретное значение xml полным документом или только фрагментом содержимого.

Создание значений XML

Чтобы получить значение типа xml из символьных данных, используйте функцию xmlparse:

XMLPARSE ( { DOCUMENT | CONTENT } value)

Примеры:

XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>')
XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')

Хотя это единственный способ преобразовать символьной строки в значения XML в соответствии со стандартом SQL, специфичные для QHB синтаксисы:

xml '<foo>bar</foo>'
'<foo>bar</foo>'::xml

также могут быть использованы.

Тип xml не проверяет входные значения по декларации типа документа (DTD), даже когда входное значение указывает DTD. В настоящее время также отсутствует встроенная поддержка проверки на соответствие другим языкам XML схем, например XML Schema.

Обратная операция, производящая символьное строковое значение из xml, использует функцию xmlserialize:

XMLSERIALIZE ( { DOCUMENT | CONTENT } value AS type )

type может быть character, character varying или text (или псевдонимом для одного из них). Опять же, согласно стандарту SQL, это единственный способ преобразования между типами xml и символьными типами, но QHB также позволяет просто приводить значение.

Когда значение символьной строки приводится к типу xml или из него без прохождения XMLPARSE или XMLSERIALIZE, соответственно, выбор DOCUMENT вместо CONTENT определяется параметром конфигурации сеанса «XML option», который можно установить с помощью стандартной команды:

SET XML OPTION { DOCUMENT | CONTENT };

или более похожий на QHB синтаксис:

SET xmloption TO { DOCUMENT | CONTENT };

По умолчанию используется CONTENT, поэтому разрешены все формы XML-данных.

Обработка кодировки

Необходимо соблюдать осторожность при работе с несколькими кодировками символов на клиенте, сервере и в данных XML, передаваемых через них. При использовании текстового режима для передачи запросов на сервер и запроса результатов к клиенту (что является нормальным режимом), QHB преобразует все символьные данные, передаваемые между клиентом и сервером, и наоборот, в кодировку символов соответствующей цели; см. раздел Поддержка набора символов. Это включает в себя строковые представления значений XML, как в приведенных выше примерах. Обычно это означает, что объявления кодировки, содержащиеся в данных XML, могут стать недействительными, поскольку символьные данные преобразуются в другие кодировки при перемещении между клиентом и сервером, поскольку объявление встроенной кодировки не изменяется. Чтобы справиться с этим поведением, объявления кодировки, содержащиеся в символьных строках, представленных для ввода в тип xml, игнорируются, и предполагается, что содержимое находится в текущей серверной кодировке. Следовательно, для правильной обработки символьные строки данных XML должны отправляться от клиента в текущей клиентской кодировке. В обязанности клиента входит либо преобразование документов в текущую клиентскую кодировку перед отправкой их на сервер, либо соответствующая настройка клиентской кодировки. На выходе значения типа xml не будут иметь объявления кодировки, и клиенты должны предполагать, что все данные находятся в текущей клиентской кодировке.

При использовании двоичного режима для передачи параметров запроса на сервер и запроса результатов обратно клиенту преобразование кодировки не выполняется, поэтому ситуация отличается. В этом случае будет соблюдаться объявление кодировки в данных XML, и если оно отсутствует, предполагается, что данные находятся в UTF-8 (как требуется стандартом XML; обратите внимание, что QHB не поддерживает UTF-16). На выходе данные будут иметь объявление кодировки, определяющее кодировку клиента, если только кодировка клиента не является UTF-8, в этом случае она будет опущена.

Нет необходимости говорить, что обработка данных XML с помощью QHB будет менее подвержена ошибкам и более эффективна, если кодировка данных XML, кодировка клиента и серверная кодировка совпадают. Поскольку данные XML обрабатываются внутри UTF-8, вычисления будут наиболее эффективными, если кодировка сервера также UTF-8.

Предупреждение!!!
Некоторые функции, связанные с XML, могут вообще не работать с данными, отличными от ASCII, если кодировка сервера не соответствует UTF-8. Известно, что это проблема, в частности, для xmltable() и xpath().

Доступ к значениям XML

Тип данных xml необычен тем, что не предоставляет операторов сравнения. Это потому, что не существует четко определенного и универсально полезного алгоритма сравнения для данных XML. Одним из следствий этого является то, что вы не можете получить строки, сравнивая столбец xml со значением поиска. Поэтому значения XML, как правило, должны сопровождаться отдельным ключевым полем, таким как идентификатор. Альтернативное решение для сравнения значений XML состоит в том, чтобы сначала преобразовать их в символьные строки, но обратите внимание, что сравнение символьных строк не имеет ничего общего с полезным методом сравнения XML.

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

Функциональность текстового поиска в QHB также может быть использована для ускорения полного поиска документов в XML-данных. Однако необходимая поддержка предварительной обработки еще не доступна в дистрибутиве QHB.

Типы JSON

Типы данных JSON предназначены для хранения данных JSON (нотации объектов JavaScript), как указано в RFC 7159. Такие данные также могут храниться в виде текста, но у типов данных JSON есть преимущество, заключающееся в том, что каждое сохраненное значение является действительным в соответствии с правилами JSON. Есть также различные JSON-специфические функции и операторы, доступные для данных, хранящихся в этих типах данных; см. раздел Функции и операторы JSON.

QHB предлагает два типа для хранения данных JSON: json и jsonb. Для реализации эффективных механизмов запросов для этих типов данных QHB также предоставляет тип данных jsonpath описанный в разделе Тип jsonpath.

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

Поскольку тип json хранит точную копию входного текста, он сохраняет семантически незначимые пробелы между токенами, а также порядок ключей в объектах JSON. Кроме того, если объект JSON внутри значения содержит один и тот же ключ более одного раза, то все пары ключ/значение сохраняются. (Функции обработки считают последнее значение как оперативное). В отличие от этого, jsonb не сохраняет пробел, не сохраняет порядок ключей объекта и не сохраняет дубликаты ключей объекта. Если во входных данных указаны дубликаты ключей, сохраняется только последнее значение.

В целом, большинство приложений предпочитают хранить данные JSON в формате jsonb, если только нет специализированных требований, таких как устаревшие предположения о порядке расположения ключей объекта.

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

RFC 7159 позволяет строкам JSON содержать escape-последовательности Unicode, обозначенные \uXXXX. В функции ввода для типа json экранирование Unicode разрешено независимо от кодировки базы данных и проверяется только на синтаксическую корректность (то есть, что четыре шестнадцатеричные цифры следуют за \u). Однако функция ввода для jsonb более строгая: она запрещает экранирование Unicode для символов, не относящихся к ASCII (те, что выше U+007F), если только кодировка базы данных не UTF8. Тип jsonb также отклоняет \u0000 (потому что это не может быть представлено в текстовом типе QHB) и настаивает на том, что любое использование суррогатных пар Unicode для обозначения символов вне базовой многоязычной плоскости Unicode является правильным. Допустимые экранированные символы Юникода преобразуются в эквивалентные символы ASCII или UTF8 для хранения; это включает в себя складывание суррогатных пар в один символ.

Заметка
Многие из функций обработки JSON, описанные в разделе Функции и операторы JSON, преобразуют экранирование Unicode в обычные символы и, следовательно, будут выдавать те же типы ошибок, которые только что были описаны, даже если их ввод имеет тип json не jsonb. Тот факт, что функция ввода json не выполняет эти проверки, может рассматриваться как исторический артефакт, хотя он допускает простое хранение (без обработки) экранирования JSON Unicode в кодировке базы данных отличной от UTF8. В общем, по возможности лучше избегать смешивания экранирования Unicode в JSON с кодировкой базы данных отличной от -UTF8, если это возможно.

При преобразовании текстового ввода JSON в jsonb примитивные типы, описанные в RFC 7159, эффективно отображаются на собственные типы QHB, как показано в Таблице 23. Следовательно, существуют некоторые незначительные дополнительные ограничения на то, что составляет допустимые данные jsonb которые не применяются ни к типу json, ни к JSON в абстрактном виде, что соответствует ограничениям на то, что может быть представлено базовым типом данных. В частности, jsonb будет отклонять числа, выходящие за пределы numeric типа данных QHB, а json - нет. Такие ограничения, определенные реализацией, разрешены RFC 7159. Однако на практике такие проблемы гораздо чаще встречаются в других реализациях, поскольку обычно тип примитива JSON представляется в виде типа с плавающей запятой двойной точности IEEE 754 (что явно предусмотрено и позволяет RFC 7159). При использовании JSON в качестве формата обмена с такими системами следует учитывать опасность потери числовой точности по сравнению с данными, изначально сохраняемыми в QHB.

И наоборот, как отмечено в таблице, существуют некоторые незначительные ограничения на формат ввода примитивных типов JSON, которые не применяются к соответствующим типам QHB.

Таблица 23. Примитивные типы JSON и соответствующие им типы QHB

Синтаксис ввода и вывода JSON

Синтаксис ввода/вывода для типов данных JSON соответствует RFC 7159.

Ниже приведены все допустимые выражения json (или jsonb):

-- Simple scalar/primitive value
-- Primitive values can be numbers, quoted strings, true, false, or null
SELECT '5'::json;

-- Array of zero or more elements (elements need not be of same type)
SELECT '[1, 2, "foo", null]'::json;

-- Object containing pairs of keys and values
-- Note that object keys must always be quoted strings
SELECT '{"bar": "baz", "balance": 7.77, "active": false}'::json;

-- Arrays and objects can be nested arbitrarily
SELECT '{"foo": [true, "bar"], "tags": {"a": 1, "b": null}}'::json;

Как указывалось ранее, когда значение JSON вводится, а затем печатается без какой-либо дополнительной обработки, json выводит тот же текст, который был введен, в то время как jsonb не сохраняет семантически незначимые детали, такие как пробелы. Например, обратите внимание на различия здесь:

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::json;
                      json                       
-------------------------------------------------
 {"bar": "baz", "balance": 7.77, "active":false}
(1 row)

SELECT '{"bar": "baz", "balance": 7.77, "active":false}'::jsonb;
                      jsonb                       
--------------------------------------------------
 {"bar": "baz", "active": false, "balance": 7.77}
(1 row)

Одна семантически незначительная деталь, на которую стоит обратить внимание, состоит в том, что в jsonb числа будут печататься в соответствии с поведением базового numeric типа. На практике это означает, что числа, введенные с пометкой E будут напечатаны без нее, например:

SELECT '{"reading": 1.230e-5}'::json, '{"reading": 1.230e-5}'::jsonb;
         json          |          jsonb          
-----------------------+-------------------------
 {"reading": 1.230e-5} | {"reading": 0.00001230}
(1 row)

Однако jsonb сохранит конечные дробные нули, как видно в этом примере, даже если они семантически незначимы для таких целей, как проверки на равенство.

Список встроенных функций и операторов, доступных для построения и обработки значений JSON, см. в разделе Функции и операторы JSON.

Разработка документов JSON

Представление данных в виде JSON может быть значительно более гибким, чем традиционная модель реляционных данных, что особенно важно в средах с изменчивыми требованиями. Оба подхода вполне могут сосуществовать и дополнять друг друга в одном приложении. Однако даже для приложений, где требуется максимальная гибкость, все же рекомендуется, чтобы документы JSON имели несколько фиксированную структуру. Структура, как правило, не является принудительной (хотя применение некоторых бизнес-правил возможно), но наличие предсказуемой структуры облегчает написание запросов, которые эффективно суммируют набор «документов» (данных) в таблице.

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

Анализ вложений и наличия в jsonb

Анализ вложений является важной возможностью jsonb. Не существует параллельного набора средств для типа json. Анализ вложений определяет, содержится ли один документ jsonb внутри другого. Эти примеры возвращают значение true, за исключением отмеченных случаев:

-- Simple scalar/primitive values contain only the identical value:
SELECT '"foo"'::jsonb @> '"foo"'::jsonb;

-- The array on the right side is contained within the one on the left:
SELECT '[1, 2, 3]'::jsonb @> '[1, 3]'::jsonb;

-- Order of array elements is not significant, so this is also true:
SELECT '[1, 2, 3]'::jsonb @> '[3, 1]'::jsonb;

-- Duplicate array elements don't matter either:
SELECT '[1, 2, 3]'::jsonb @> '[1, 2, 2]'::jsonb;

-- The object with a single pair on the right side is contained
-- within the object on the left side:
SELECT '{"product": "PostgreSQL", "version": 9.4, "jsonb": true}'::jsonb @> '{"version": 9.4}'::jsonb;

-- The array on the right side is not considered contained within the
-- array on the left, even though a similar array is nested within it:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[1, 3]'::jsonb;  -- yields false

-- But with a layer of nesting, it is contained:
SELECT '[1, 2, [1, 3]]'::jsonb @> '[[1, 3]]'::jsonb;

-- Similarly, containment is not reported here:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"bar": "baz"}'::jsonb;  -- yields false

-- A top-level key and an empty object is contained:
SELECT '{"foo": {"bar": "baz"}}'::jsonb @> '{"foo": {}}'::jsonb;

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

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

-- This array contains the primitive string value:
SELECT '["foo", "bar"]'::jsonb @> '"bar"'::jsonb;

-- This exception is not reciprocal -- non-containment is reported here:
SELECT '"bar"'::jsonb @> '["bar"]'::jsonb;  -- yields false

В jsonb также есть оператор наличия (existence), который является вариацией темы вложения: он проверяет, отображается ли строка (заданная как текстовое значение) как ключ объекта или элемент массива на верхнем уровне значения jsonb. Эти примеры возвращают значение true, за исключением отмеченных случаев:

-- String exists as array element:
SELECT '["foo", "bar", "baz"]'::jsonb ? 'bar';

-- String exists as object key:
SELECT '{"foo": "bar"}'::jsonb ? 'foo';

-- Object values are not considered:
SELECT '{"foo": "bar"}'::jsonb ? 'bar';  -- yields false

-- As with containment, existence must match at the top level:
SELECT '{"foo": {"bar": "baz"}}'::jsonb ? 'bar'; -- yields false

-- A string is considered to exist if it matches a primitive JSON string:
SELECT '"foo"'::jsonb ? 'foo';

Объекты JSON лучше, чем массивы, подходят для анализа вложений или наличия, когда задействовано много ключей или элементов, поскольку в отличие от массивов они внутренне оптимизированы для поиска и не требуют линейного поиска.

Заметка
Поскольку содержимое JSON является вложенным, соответствующий запрос может пропустить явный выбор подобъектов. В качестве примера предположим, что у нас есть столбец doc содержащий объекты на верхнем уровне, а большинство объектов содержат поля tags которые содержат массивы подобъектов. Этот запрос находит записи, в которых появляются "term":"paris" содержащие как "term":"paris" и "term":"food", игнорируя любые такие ключи вне массива tags:

```sql
SELECT doc->'site_name' FROM websites
  WHERE doc @> '{"tags":[{"term":"paris"}, {"term":"food"}]}';
```

Можно сделать то же самое, скажем,

```sql
SELECT doc->'site_name' FROM websites
  WHERE doc->'tags' @> '[{"term":"paris"}, {"term":"food"}]';
```

но такой подход менее гибок, а зачастую и менее эффективен.

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

Различные операторы вложений и наличия, а также все другие операторы и функции JSON описаны в разделе Функции и операторы JSON.

Индексация jsonb

Индексы GIN можно использовать для эффективного поиска ключей или пар ключ/значение, встречающихся в большом количестве документов jsonb (датумов). Предоставляются два «класса операторов» GIN, предлагающих различные компромиссы производительности и гибкости.

Класс операторов GIN по умолчанию для jsonb поддерживает запросы с операторами верхнего уровня наличия ключей: ?, ?& и ?| и оператор наличия пути/значения @>. (Подробнее о семантике, которую реализуют эти операторы, см. таблицу 45). Пример создания индекса с помощью этого класса операторов:

CREATE INDEX idxgin ON api USING GIN (jdoc);

Нестандартный класс операторов GIN jsonb_path_ops поддерживает индексирование только оператора @>. Пример создания индекса с помощью этого класса операторов:

CREATE INDEX idxginp ON api USING GIN (jdoc jsonb_path_ops);

Рассмотрим пример таблицы, в которой хранятся документы JSON, извлеченные из стороннего веб-сервиса, с документированным определением схемы. Типичный документ:

{
    "guid": "9c36adc1-7fb5-4d5b-83b4-90356a46061a",
    "name": "Angela Barton",
    "is_active": true,
    "company": "Magnafone",
    "address": "178 Howard Place, Gulf, Washington, 702",
    "registered": "2009-11-07T08:53:22 +08:00",
    "latitude": 19.793713,
    "longitude": 86.513373,
    "tags": [
        "enim",
        "aliquip",
        "qui"
    ]
}

Мы храним эти документы в таблице с именем api, в столбце jsonb именем jdoc. Если для этого столбца создается индекс GIN, индекс может использовать такие запросы:

-- Find documents in which the key "company" has value "Magnafone"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"company": "Magnafone"}';

Тем не менее, индекс не может быть использован для запросов, подобных следующему, потому что хотя оператор ? индексируется, он не применяется непосредственно к индексируемому столбцу jdoc:

-- Find documents in which the key "tags" contains key or array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc -> 'tags' ? 'qui';

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

CREATE INDEX idxgintags ON api USING GIN ((jdoc -> 'tags'));

Теперь, WHERE предложение jdoc -> 'tags' ? 'qui' будет распознаваться как приложение индексируемого оператора ? к индексированному выражению jdoc -> 'tags'. (Более подробную информацию об индексах выражений можно найти в разделе Индексы по выражениям).

Кроме того, индекс GIN поддерживает @@ и @? операторы, которые выполняют сопоставление jsonpath.

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] == "qui"';

SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @@ '$.tags[*] ? (@ == "qui")';

Индекс GIN извлекает из jsonpath операторы следующей формы: accessors_chain = const. Цепочка доступа может состоять из методов доступа .key, [*] и [index]. jsonb_ops дополнительно поддерживает методы доступа .* и .**.

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

-- Find documents in which the key "tags" contains array element "qui"
SELECT jdoc->'guid', jdoc->'name' FROM api WHERE jdoc @> '{"tags": ["qui"]}';

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

Хотя класс операторов jsonb_path_ops поддерживает только запросы с операторами @>, @@ и @?, он имеет заметные преимущества в производительности по сравнению с классом операторов по умолчанию jsonb_ops. Индекс jsonb_path_ops обычно намного меньше индекса jsonb_ops на тех же данных, и специфика поиска лучше, особенно когда запросы содержат ключи, которые часто появляются в данных. Поэтому поисковые операции с индексом jsonb_path_ops обычно выполняются лучше, чем с классом операторов по умолчанию.

Техническое различие между индексами GIN jsonb_ops и jsonb_path_ops состоит в том, что первый создает независимые элементы индекса для каждого ключа и значения в данных, а второй создает элементы индекса только для каждого значения в данных.¹ По сути, каждый элемент индекса jsonb_path_ops является хешем значения и ключа (ключей), ведущих к нему; например, чтобы индексировать {"foo": {"bar": "baz"}}, будет создан один элемент индекса, включающий все три элемента foo, bar и baz в хеш- значение. Таким образом, запрос вложений, ищущий эту структуру, приведет к чрезвычайно специфичному поиску по индексу; но нет никакого способа узнать, является ли foo ключом. С другой стороны, индекс jsonb_ops будет создавать три элемента индекса, представляющих foo, bar и baz отдельно; затем, чтобы выполнить запрос вложений, он будет искать строки, содержащие все три этих элемента. Хотя индексы GIN могут выполнять такой поиск довольно эффективно, он все равно будет менее конкретным и более медленным, чем эквивалентный поиск jsonb_path_ops, особенно если существует очень большое количество строк, содержащих какой-либо один из трех элементов индекса.

Недостаток подхода jsonb_path_ops заключается в том, что он не создает индексных записей для структур JSON, не содержащих никаких значений, таких как {"a": {}}. Если запрашивается поиск документов, содержащих такую структуру, то для этого потребуется полное индексное сканирование, которое выполняется довольно медленно. Поэтому jsonb_path_ops не подходит для приложений, которые часто выполняют такой поиск.

jsonb также поддерживает индексы btree и hash. Обычно они полезны, только если важно проверить равенство полных документов JSON. Упорядочение btree для данных jsonb редко представляет большой интерес, но для полноты он является:

Object > Array > Boolean > Number > String > Null

Object with n pairs > object with n - 1 pairs

Array with n elements > array with n - 1 elements

Объекты с одинаковым количеством пар сравниваются в следующем порядке:

key-1, value-1, key-2 ...

Обратите внимание, что ключи объектов сравниваются в порядке их хранения; в частности, поскольку более короткие ключи хранятся перед более длинными ключами, это может привести к неинтуитивным результатам, таким как:

{ "aa": 1, "c": 1} > {"b": 1, "d": 1}

Аналогично, массивы с равным количеством элементов сравниваются в следующем порядке:

element-1, element-2 ...

Примитивные значения JSON сравниваются с использованием тех же правил сравнения, что и для базового типа данных QHB. Строки сравниваются с использованием параметров сортировки базы данных по умолчанию.

Трансформации

Доступны дополнительные расширения, которые реализуют преобразования для типа jsonb для разных процедурных языков.

Тип jsonpath

Тип jsonpath реализует поддержку языка путей SQL/JSON в QHB для эффективного запроса данных JSON. Он предоставляет двоичное представление разобранного выражения пути SQL/JSON, которое указывает элементы, которые должны быть извлечены механизмом пути из данных JSON для дальнейшей обработки с помощью функций запроса SQL/JSON.

Семантика предикатов и операторов пути SQL/JSON обычно соответствует SQL. В то же время, чтобы обеспечить наиболее естественный способ работы с данными JSON, синтаксис пути SQL/JSON использует некоторые соглашения JavaScript:

• Точка (.) Используется для доступа членов.

• Квадратные скобки ([]) используются для доступа к массиву.

• Массивы SQL/JSON начинаются с 0, в отличие от обычных массивов SQL, которые начинаются с 1.

Выражение пути SQL/JSON обычно пишется в запросе SQL в виде строкового литерала SQL, поэтому его необходимо заключить в одинарные кавычки, а любые одинарные кавычки, требуемые в пределах значения, должны быть удвоены (см. раздел Строковые константы). Некоторые формы выражений пути требуют строковых литералов внутри них. Эти встроенные строковые литералы следуют соглашениям JavaScript/ECMAScript: они должны быть заключены в двойные кавычки, и в них могут использоваться экранированные символы обратной косой черты для представления символов, трудно поддающихся вводу. В частности, способ написать двойную кавычку во встроенном строковом литерале — это \", а чтобы написать обратную косую черту, нужно написать \\. Другие специальные последовательности обратной косой черты включают в себя те, которые распознаются в строках JSON: \b, \f, \n, \r, \t, \v для различных управляющих символов ASCII и \uNNNN для символа Unicode, идентифицируемого его кодовой точкой из 4 шестнадцатеричных цифр. Синтаксис обратной косой черты также включает два случая, которые не допускаются JSON: \xNN для символьного кода, написанного только с двумя шестнадцатеричными цифрами, и \u{N...} для символьного кода, записанного с помощью от 1 до 6 шестнадцатеричных цифр.

Выражение пути состоит из последовательности элементов пути, которая может быть следующей:

• Литералы пути примитивных типов JSON:Unicode text, numeric, true, false или null.

• Переменные пути, перечисленные в Таблице 24.

• Операторы доступа перечислены в Таблице 25.

• Операторы и методы jsonpath, перечисленные в разделе Операторы пути и методы SQL/JSON

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

Подробнее об использовании выражений jsonpath с функциями запросов SQL/JSON см. раздел Язык путей SQL/JSON.

Таблица 24. Переменные jsonpath

Таблица 25. Операторы доступа jsonpath

Массивы

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

Объявление типов массивов

Чтобы проиллюстрировать использование типов массивов, мы создадим эту таблицу:

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

Как показано, тип данных массива именуется путем добавления квадратных скобок ([]) к имени типа данных элементов массива. Приведенная выше команда создаст таблицу с именем sal_emp со столбцом типа text (name), одномерным массивом типа integer (pay_by_quarter), который представляет квартальную зарплату сотрудника, и двумерным массивом text (schedule), который представляет недельный график сотрудника.

Синтаксис CREATE TABLE позволяет указывать точный размер массивов, например:

CREATE TABLE tictactoe (
    squares   integer[3][3]
);

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

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

Альтернативный синтаксис, который соответствует стандарту SQL с помощью ключевого слова ARRAY, может использоваться для одномерных массивов. pay_by_quarter мог быть определен как:

pay_by_quarter integer ARRAY[4],

Или, если не указан размер массива:

pay_by_quarter integer ARRAY,

Однако, как и прежде, QHB не налагает ограничения на размер в любом случае.

Ввод значений массива

Чтобы записать значение массива в виде литеральной константы, заключите значения элементов в фигурные скобки и разделите их запятыми. (Если вы знаете C/RUST, это мало чем отличается от синтаксиса C для инициализации структур). Вы можете поставить двойные кавычки вокруг любого значения элемента и должны сделать это, если он содержит запятые или фигурные скобки. (Подробнее см. ниже). Таким образом, общий формат константы массива следующий:

'{ val1 delim val2 delim ... }'

где delim - символ разделителя для типа, как отмечено в записи pg_type. Среди стандартных типов данных, представленных в дистрибутиве QHB, все используют запятую (,), за исключением типа box, в котором используется точка с запятой (;). Каждый val является либо константой типа элемента массива, либо подмножеством. Пример константы массива:

'{{1,2,3},{4,5,6},{7,8,9}}'

Эта константа представляет собой двумерный массив размером 3 на 3, состоящий из трех подмножеств целых чисел.

Чтобы установить для элемента константы массива значение NULL, напишите NULL для значения элемента. (Подойдет любой вариант NULL верхнем или нижнем регистре). Если вам нужно фактическое строковое значение «NULL», вы должны заключить его в двойные кавычки.

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

Теперь мы можем показать некоторые операторы INSERT:

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

Результат двух предыдущих вставок выглядит так:

SELECT * FROM sal_emp;
 name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
 Bill  | {10000,10000,10000,10000} | {{meeting,lunch},{training,presentation}}
 Carol | {20000,25000,25000,25000} | {{breakfast,consulting},{meeting,lunch}}
(2 rows)

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

INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"meeting"}}');
ERROR:  multidimensional arrays must have array expressions with matching dimensions

Синтаксис конструктора ARRAY также может быть использован:

INSERT INTO sal_emp
    VALUES ('Bill',
    ARRAY[10000, 10000, 10000, 10000],
    ARRAY[['meeting', 'lunch'], ['training', 'presentation']]);

INSERT INTO sal_emp
    VALUES ('Carol',
    ARRAY[20000, 25000, 25000, 25000],
    ARRAY[['breakfast', 'consulting'], ['meeting', 'lunch']]);

Обратите внимание, что элементы массива являются обычными константами или выражениями SQL; например, строковые литералы заключаются в одинарные кавычки, а не в двойные, как это было бы в литерале массива. Синтаксис конструктора ARRAY более подробно обсуждается в разделе Конструкторы массивов.

Доступ к массивам

Теперь мы можем выполнить несколько запросов к таблице. Сначала мы покажем, как получить доступ к одному элементу массива. Этот запрос извлекает имена сотрудников, зарплата которых изменилась во втором квартале:

SELECT name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2];

 name
-------
 Carol
(1 row)

Номера индексов массива пишутся в квадратных скобках. По умолчанию QHB использует для массивов соглашение об одинарной нумерации, то есть массив из n элементов начинается с array[1] и заканчивается array[n].

Этот запрос возвращает заработную плату всех сотрудников за третий квартал:

SELECT pay_by_quarter[3] FROM sal_emp;

 pay_by_quarter
----------------
          10000
          25000
(2 rows)

Мы также можем получить доступ к произвольным прямоугольным фрагментам массива или подмассива. Срез массива обозначается записью «нижняя граница»:«верхняя граница» для одного или нескольких измерений массива. Например, этот запрос извлекает первый элемент в расписании Билла за первые два дня недели:

SELECT schedule[1:2][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Если какое-либо измерение записывается как срез, т. е. содержит двоеточие, то все измерения рассматриваются как срезы. Любое измерение, которое имеет только одно число (без двоеточия), рассматривается как значение от 1 до указанного числа. Например, [2] обрабатывается как [1:2], как в этом примере:

SELECT schedule[1:2][2] FROM sal_emp WHERE name = 'Bill';

                 schedule
-------------------------------------------
 {{meeting,lunch},{training,presentation}}
(1 row)

Чтобы избежать путаницы со случаем без срезов, лучше использовать синтаксис срезов для всех измерений, например, [1:2][1:1], а не [2][1:1].

Можно опустить нижнюю и/или верхнюю границу спецификатора слайса; отсутствующая граница заменяется нижним или верхним пределом индексов массива. Например:

SELECT schedule[:2][2:] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{lunch},{presentation}}
(1 row)

SELECT schedule[:][1:1] FROM sal_emp WHERE name = 'Bill';

        schedule
------------------------
 {{meeting},{training}}
(1 row)

Выражение индекса массива вернет значение NULL, если либо сам массив, либо любое из выражений индекса равно NULL. Кроме того, null возвращается, если индекс находится за пределами границ массива (в этом случае ошибка не возникает). Например, если schedule в настоящее время имеет размеры [1:3][1:2] то ссылка на schedule[3][3] выдает NULL. Точно так же ссылка на массив с неправильным числом индексов дает NULL значение, а не ошибку.

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

Текущие измерения любого значения массива можно получить с помощью функции array_dims:

SELECT array_dims(schedule) FROM sal_emp WHERE name = 'Carol';

 array_dims
------------
 [1:2][1:2]
(1 row)

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

SELECT array_lower(schedule, 1), array_upper(schedule, 1) FROM sal_emp WHERE name = 'Carol';

  array_lower array_upper
--------------------------
  1           2
(1 row)

array_length вернет длину указанного размера массива:

SELECT array_length(schedule, 1) FROM sal_emp WHERE name = 'Carol';

 array_length
--------------
            2
(1 row)

cardinality возвращает общее количество элементов в массиве по каждому измерению. Это фактическое число строк, которое возвращает вызов unnest:

SELECT cardinality(schedule) FROM sal_emp WHERE name = 'Carol';

 cardinality
-------------
           4
(1 row)

Модификация массивов

Значение массива можно полностью заменить:

UPDATE sal_emp SET pay_by_quarter = '{25000,25000,27000,27000}'
    WHERE name = 'Carol';

или используя синтаксис выражения ARRAY:

UPDATE sal_emp SET pay_by_quarter = ARRAY[25000,25000,27000,27000]
    WHERE name = 'Carol';

Массив также можно обновить в одном элементе:

UPDATE sal_emp SET pay_by_quarter[4] = 15000
    WHERE name = 'Bill';

или обновить в срезе:

UPDATE sal_emp SET pay_by_quarter[1:2] = '{27000,27000}'
    WHERE name = 'Carol';

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

Сохраненный массив можно расширять, добавляя элементы, которые еще не присутствуют. Любые позиции между ранее присутствующими и вновь назначенными элементами будут заполнены null. Например, если массив myarray в настоящее время имеет 4 элемента, он будет иметь шесть элементов после обновления, которое присваивается myarray[6]; myarray[5]будет содержать null. В настоящее время расширение таким способом допускается только для одномерных, а не многомерных массивов.

Назначение индексов позволяет создавать массивы, в которых не используются одинарная нумерация. Например, можно назначить myarray[-2:7] для создания массива со значениями индекса от -2 до 7.

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

SELECT ARRAY[1,2] || ARRAY[3,4];
 ?column?
-----------
 {1,2,3,4}
(1 row)

SELECT ARRAY[5,6] || ARRAY[[1,2],[3,4]];
      ?column?
---------------------
 {{5,6},{1,2},{3,4}}
(1 row)

Оператор конкатенации позволяет помещать отдельный элемент в начало или конец одномерного массива. Он также принимает два N-мерных массива или N-мерный и N+1-мерный массив.

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

SELECT array_dims(1 || '[0:1]={2,3}'::int[]);
 array_dims
------------
 [0:2]
(1 row)

SELECT array_dims(ARRAY[1,2] || 3);
 array_dims
------------
 [1:3]
(1 row)

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

SELECT array_dims(ARRAY[1,2] || ARRAY[3,4,5]);
 array_dims
------------
 [1:5]
(1 row)

SELECT array_dims(ARRAY[[1,2],[3,4]] || ARRAY[[5,6],[7,8],[9,0]]);
 array_dims
------------
 [1:5][1:2]
(1 row)

Когда N-мерный массив помещается в начало или конец N+1-мерного массива, результат аналогичен описанному выше случаю массива элементов. Каждый N-мерный подмассив по сути является элементом внешнего измерения N+1-мерного массива. Например:

SELECT array_dims(ARRAY[1,2] || ARRAY[[3,4],[5,6]]);
 array_dims
------------
 [1:3][1:2]
(1 row)

Массив также может быть создан с использованием функций array_prepend, array_append или array_cat. Первые два поддерживают только одномерные массивы, но array_cat поддерживает многомерные массивы. Несколько примеров:

SELECT array_prepend(1, ARRAY[2,3]);
 array_prepend
---------------
 {1,2,3}
(1 row)

SELECT array_append(ARRAY[1,2], 3);
 array_append
--------------
 {1,2,3}
(1 row)

SELECT array_cat(ARRAY[1,2], ARRAY[3,4]);
 array_cat
-----------
 {1,2,3,4}
(1 row)

SELECT array_cat(ARRAY[[1,2],[3,4]], ARRAY[5,6]);
      array_cat
---------------------
 {{1,2},{3,4},{5,6}}
(1 row)

SELECT array_cat(ARRAY[5,6], ARRAY[[1,2],[3,4]]);
      array_cat
---------------------
 {{5,6},{1,2},{3,4}}

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

SELECT ARRAY[1, 2] || '{3, 4}';  -- the untyped literal is taken as an array
 ?column?
-----------
 {1,2,3,4}

SELECT ARRAY[1, 2] || '7';                 -- so is this one
ERROR:  malformed array literal: "7"

SELECT ARRAY[1, 2] || NULL;                -- so is an undecorated NULL
 ?column?
----------
 {1,2}
(1 row)

SELECT array_append(ARRAY[1, 2], NULL);    -- this might have been meant
 array_append
--------------
 {1,2,NULL}

В приведенных выше примерах анализатор видит целочисленный массив на одной стороне оператора конкатенации и константу неопределенного типа с другой. Эвристика, которую он использует для разрешения типа константы, предполагает, что она того же типа, что и другие входные данные оператора - в данном случае, целочисленный массив. Поэтому предполагается, что оператор конкатенации представляет array_cat, а не array_append. Если это неправильный выбор, его можно исправить, приведя константу к типу элемента массива; но явное использование array_append может быть предпочтительным решением.

Поиск в массивах

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

SELECT * FROM sal_emp WHERE pay_by_quarter[1] = 10000 OR
                            pay_by_quarter[2] = 10000 OR
                            pay_by_quarter[3] = 10000 OR
                            pay_by_quarter[4] = 10000;

Однако это быстро становится утомительным для больших массивов и бесполезно, если размер массива неизвестен. Альтернативный метод описан в разделе Сравнение строк и массивов. Приведенный выше запрос может быть заменен на:

SELECT * FROM sal_emp WHERE 10000 = ANY (pay_by_quarter);

Кроме того, вы можете найти строки, где массив имеет все значения, равные 10000, с помощью:

SELECT * FROM sal_emp WHERE 10000 = ALL (pay_by_quarter);

В качестве альтернативы можно использовать функцию generate_subscripts. Например:

SELECT * FROM
   (SELECT pay_by_quarter,
           generate_subscripts(pay_by_quarter, 1) AS s
      FROM sal_emp) AS foo
 WHERE pay_by_quarter[s] = 10000;

Эта функция описана в разделе Функции возврата наборов (SET).

Вы также можете искать в массиве с помощью оператора &&, который проверяет, перекрывает ли левый операнд правый операнд. Например:

SELECT * FROM sal_emp WHERE pay_by_quarter && ARRAY[10000];

Этот и другие операторы массива более подробно описаны в разделе Функции и операторы массива. Его можно ускорить с помощью соответствующего индекса, как описано в разделе Типы индексов.

Вы также можете искать конкретные значения в массиве, используя функции array_position и array_positions. Первый возвращает индекс первого вхождения значения в массиве; последний возвращает массив с индексами всех вхождений значения в массиве. Например:

SELECT array_position(ARRAY['sun','mon','tue','wed','thu','fri','sat'], 'mon');
 array_positions
-----------------
 2

SELECT array_positions(ARRAY[1, 4, 3, 1, 3, 4, 2, 1], 1);
 array_positions
-----------------
 {1,4,8}

Заметка
Массивы не являются наборами; поиск определенных элементов массива может быть признаком неправильного проектирования базы данных. Попробуйте использовать отдельную таблицу со строкой для каждого элемента, который будет являться элементом массива. Это будет легче для поиска, и, вероятно, позволит лучше масштабировать большое количество элементов.

Синтаксис ввода и вывода массива

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

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

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

SELECT f1[1][-2][3] AS e1, f1[1][-1][5] AS e2
 FROM (SELECT '[1:1][-2:-1][3:5]={{{1,2,3},{4,5,6}}}'::int[] AS f1) AS ss;

 e1 | e2
----+----
  1 |  6
(1 row)

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

Если значение, записанное для элемента, равно NULL (в любом случае), элемент считается равным NULL. Наличие любых кавычек или обратной косой черты отключает это и позволяет вводить буквальное строковое значение литерала «NULL».

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

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

Заметка
Синтаксис конструктора массива (см. раздел Конструкторы массивов) часто проще в работе, чем синтаксис массива-литерала при записи значений массива в командах SQL. В массиве значения отдельных элементов записываются так же, как если бы они не были членами массива.

Составные типы

Составной тип представляет структуру строки или записи; по существу, это просто список имен полей и их типов данных. QHB позволяет использовать составные типы во многом так же, как и простые типы. Например, столбец таблицы может быть объявлен как составной тип.

Декларация составных типов

Вот два простых примера определения составных типов:

CREATE TYPE complex AS (
    r       double precision,
    i       double precision
);

CREATE TYPE inventory_item AS (
    name            text,
    supplier_id     integer,
    price           numeric
);

Синтаксис сопоставим с CREATE TABLE, за исключением того, что можно указывать только имена и типы полей; никакие ограничения (такие как NOT NULL) в настоящее время не могут быть включены. Обратите внимание, что ключевое слово AS имеет важное значение; без этого система будет думать, что подразумевается другого вид команды CREATE TYPE, и вы получите странные синтаксические ошибки.

Определив типы, мы можем использовать их для создания таблиц:

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);

или функции:

CREATE FUNCTION price_extension(inventory_item, integer) RETURNS numeric
AS 'SELECT $1.price * $2' LANGUAGE SQL;

SELECT price_extension(item, 10) FROM on_hand;

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

CREATE TABLE inventory_item (
    name            text,
    supplier_id     integer REFERENCES suppliers,
    price           numeric CHECK (price > 0)
);

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

Построение составных значений

Чтобы записать составное значение в виде литеральной константы, заключите значения полей в круглые скобки и разделите их запятыми. Вы можете поместить двойные кавычки вокруг любого значения поля, и это необходимо делать, если оно содержит запятые или скобки. (Подробнее см. ниже). Таким образом, общий формат составной константы следующий:

'( val1, val2, ... )'

Примером является:

'("fuzzy dice",42,1.99)'

что будет допустимым значением типа inventory_item определенного выше. Чтобы сделать поле пустым, не пишите никаких символов в его позиции в списке. Например, эта константа задает третье поле NULL:

'("fuzzy dice",42,)'

Если нужна пустая строка, а не NULL, напишите двойные кавычки:

'("",42,)'

Здесь первое поле является не-NULL пустой строкой, третье - NULL.

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

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

ROW('fuzzy dice', 42, 1.99)
ROW('', 42, NULL)

Ключевое слово ROW на самом деле является необязательным, если в выражении указано более одного поля, поэтому его можно упростить до:

('fuzzy dice', 42, 1.99)
('', 42, NULL)

Синтаксис конструктора строки более подробно обсуждается в разделе Конструкторы строк.

Доступ к составным типам

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

SELECT item.name FROM on_hand WHERE item.price > 9.99;

Это не будет работать, так как элемент item считается именем таблицы, а не именем столбца on_hand, согласно правилам синтаксиса SQL. Вы должны написать это так:

SELECT (item).name FROM on_hand WHERE (item).price > 9.99;

или если вам нужно использовать имя таблицы (например, в запросе с несколькими таблицами), например так:

SELECT (on_hand.item).name FROM on_hand WHERE (on_hand.item).price > 9.99;

Теперь заключенный в скобки объект правильно интерпретируется как ссылка на столбец item, а затем из него можно выбрать субполе.

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

SELECT (my_func(...)).field FROM ...

Без лишних скобок это приведет к синтаксической ошибке.

Название специального поля * означает «все поля», как более подробно описано в разделе Использование составных типов в запросах.

Модификация составных типов

Вот несколько примеров правильного синтаксиса для вставки и обновления составных столбцов. Во-первых, вставка или обновление целого столбца:

INSERT INTO mytab (complex_col) VALUES((1.1,2.2));

UPDATE mytab SET complex_col = ROW(1.1,2.2) WHERE ...;

Первый пример пропускает ROW, второй использует его; мы могли бы сделать это в любом случае.

Мы можем обновить отдельное подполе составного столбца:

UPDATE mytab SET complex_col.r = (complex_col).r + 1 WHERE ...;

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

И мы можем указать подполя как цели для INSERT:

INSERT INTO mytab (complex_col.r, complex_col.i) VALUES(1.1, 2.2);

Если бы мы не указали значения для всех подполей столбца, оставшиеся подполя были бы заполнены нулевыми значениями.

Использование составных типов в запросах

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

В QHB ссылка на имя таблицы (или псевдоним) в запросе фактически является ссылкой на составное значение текущей строки таблицы. Например, если бы у нас была таблица inventory_item как показано в разделе Декларация составных типов, мы могли бы написать:

SELECT c FROM inventory_item c;

Этот запрос создает один составной столбец, поэтому мы можем получить вывод, например:

c
------------------------
("fuzzy dice",42,1.99)
(1 row)

Однако обратите внимание, что простые имена сопоставляются с именами столбцов перед именами таблиц, поэтому этот пример работает только потому, что в таблицах запроса нет столбца с именем c.

Обычный синтаксис имени квалифицированного столбца table_name.column_name можно понимать как применение выбора поля к составному значению текущей строки таблицы. (Из соображений эффективности это не реализовано таким образом).

Когда мы пишем

SELECT c.* FROM inventory_item c;

тогда, согласно стандарту SQL, мы должны расширить содержимое таблицы на отдельные столбцы:

name       | supplier_id | price
-----------+-------------+-------
fuzzy dice |          42 |  1.99
(1 row)

как будто выполнялся запрос:

SELECT c.name, c.supplier_id, c.price FROM inventory_item c;

QHB будет применять это поведение раскрытия к любому составному выражению, хотя, как показано выше, вам нужно писать круглые скобки вокруг значения, к которому применяется .*, когда это не простое имя таблицы. Например, если myfunc() - это функция, возвращающая составной тип со столбцами a, b и c, то эти два запроса имеют одинаковый результат:

SELECT (myfunc(x)).* FROM some_table;
SELECT (myfunc(x)).a, (myfunc(x)).b, (myfunc(x)).c FROM some_table;

Заметка
QHB обрабатывает расширение столбцов, фактически превращая первую форму во вторую. Таким образом, в этом примере myfunc() будет вызываться три раза в строке с любым синтаксисом. Если это дорогая функция, вы можете избежать этого, с помощью запроса:

`SELECT m.* FROM some_table, LATERAL myfunc(x) AS m;`

Помещение функции в боковой элемент FROM позволяет вызывать ее более
одного раза в строке. `m.*` все еще разворачивается в `m.a`, `m.b`, `m.c`, но
теперь эти переменные являются просто ссылками на выходные данные
элемента *FROM*. (Ключевое слово *LATERAL* здесь необязательно, но мы
показываем его, чтобы уточнить, что функция получает `x` из *some_table*).

Синтаксис composite_value.* приводит к расширению столбца такого типа, когда он появляется на верхнем уровне выходного списка SELECT, списка RETURNING в INSERT/UPDATE/DELETE, предложения VALUES или конструктора строки. Во всех других контекстах (в том числе, когда они вложены в одну из этих конструкций), присоединение .* к составному значению не меняет значение, поскольку означает «все столбцы», и поэтому то же составное значение создается снова. Например, если somefunc() принимает составной аргумент, эти запросы совпадают:

SELECT somefunc(c.*) FROM inventory_item c;
SELECT somefunc(c) FROM inventory_item c;

В обоих случаях текущая строка inventory_item передается функции в виде одного составного аргумента. Несмотря на то, что .* ничего не делает в таких случаях, это хороший стиль, поскольку он ясно показывает, что предполагается составное значение. В частности, синтаксический анализатор будет рассматривать c как c.* для ссылки на имя таблицы или псевдоним, а не на имя столбца, так что нет никакой двусмысленности; тогда как без .* неясно, означает ли c имя таблицы или имя столбца, и на самом деле интерпретация имени столбца будет предпочтительнее, если есть столбец с именем c.

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

SELECT * FROM inventory_item c ORDER BY c;
SELECT * FROM inventory_item c ORDER BY c.*;
SELECT * FROM inventory_item c ORDER BY ROW(c.*);

Все эти предложения ORDER BY задают составное значение строки, в результате чего строки сортируются в соответствии с правилами, описанными в разделе Сравнение составных типов. Однако если inventory_item элемент содержит столбец с именем «c», то первый случай будет отличаться от других, поскольку это означало бы сортировку только по этому столбцу. Учитывая имена столбцов, показанные ранее, эти запросы также эквивалентны приведенным выше:

SELECT * FROM inventory_item c ORDER BY ROW(c.name, c.supplier_id, c.price);
SELECT * FROM inventory_item c ORDER BY (c.name, c.supplier_id, c.price);

(В последнем случае используется конструктор строки с опущенным ключевым словом ROW).

Другое специальное синтаксическое поведение, связанное с составными значениями, заключается в том, что мы можем использовать функциональную нотацию для извлечения поля составного значения. Простой способ объяснить это состоит в том, что нотации field(table) и table.field являются взаимозаменяемыми. Например, эти запросы эквивалентны:

SELECT c.name FROM inventory_item c WHERE c.price > 1000;
SELECT name(c) FROM inventory_item c WHERE price(c) > 1000;

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

SELECT somefunc(c) FROM inventory_item c;
SELECT somefunc(c.*) FROM inventory_item c;
SELECT c.somefunc FROM inventory_item c;

Эта эквивалентность между функциональной нотацией и нотацией полей позволяет использовать функции на составных типах для реализации «вычисляемых полей». Приложению, использующему последний запрос выше, не нужно знать, что somefunc не является реальным столбцом таблицы.

Синтаксис ввода и вывода составного типа

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

'( 42)'

пробел будет игнорироваться, если тип поля целочисленный, но не текстовый.

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

Абсолютно пустое значение поля (без запятых или круглых скобок) означает NULL. Чтобы записать значение, которое является пустой строкой, а не NULL, напишите "".

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

Заметка
Помните, что то, что вы пишете в команде SQL, сначала будет интерпретироваться как строковый литерал, а затем как составной. Это удваивает количество обратных косых черт, которые вам нужны (при условии использования синтаксиса escape-строки). Например, чтобы вставить текстовое поле, содержащее двойную кавычку и обратную косую черту в составном значении, вам нужно написать:

```sql
INSERT ... VALUES ('("\"\\")');
```

Обработчик строковых литералов удаляет один уровень обратной косой черты, так что то, что поступает в синтаксический анализатор составных значений, выглядит как ("\"\\"). В свою очередь, строка, переданная подпрограмме ввода типа данных text, становится "\. (Если бы мы работали с типом данных, чья подпрограмма ввода также обрабатывала обратную косую черту, например, bytea, нам может потребоваться до восьми обратных косых черт в команде, чтобы получить одну обратную косую черту в сохраненном составном поле). Кавычки из "долларов" (см. раздел Строковые константы в долларовых кавычках) могут использоваться, чтобы избежать необходимости удваивать обратную косую черту.

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

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

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

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

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

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

• int4range - диапазон integer
• int8range - диапазон от bigint
• numrange - диапазон numeric значений
• tsrange - диапазон timestamp without time zone
• tstzrange - диапазон timestamp with time zone
• daterange - диапазон date

Кроме того, вы можете определить свои собственные типы диапазонов; см. 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);

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

См. раздел Функции диапазона и операторы для получения полного списка операторов и функций для типов диапазонов.

Инклюзивные и эксклюзивные границы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примеры:

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

Построение рядов

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

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

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

Дискретный диапазон — это тот, чей тип элемента имеет четко определенный «шаг», такой как 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 должна указывать функцию канонизации. Функция канонизации принимает значение входного диапазона и должна возвращать эквивалентное значение диапазона, которое может иметь различные границы и форматирование. Канонический вывод для двух диапазонов, представляющих один и тот же набор значений, например целочисленные диапазоны [1, 7] и [1, 8), должен быть одинаковым. Неважно, какое представление вы выберете как каноническое, если два эквивалентных значения с разными форматами всегда отображаются на одно и то же значение с одинаковым форматированием. В дополнение к настройке формата включающих/исключающих границ функция канонизации может округлять граничные значения в случае, если желаемый размер шага больше, чем тот, который способен хранить подтип. Например, тип диапазона по метке времени может быть определен так, чтобы иметь размер шага в час, и в этом случае функция канонизации должна будет округлить границы, не кратные часу, или, возможно, вместо этого выдавать ошибку.

Кроме того, любой тип диапазона, который предназначен для использования с индексами GiST или SP-GiST, должен определять разность подтипов или функцию subtype_diff. (Индекс по-прежнему будет работать без subtype_diff, но он, вероятно, будет значительно менее эффективным, чем если бы была предусмотрена разностная функция). Разностная функция подтипа принимает два входных значения подтипа и возвращает их разность (то есть X минус Y) представляется как значение float8. В примере выше может использоваться функция float8mi которая лежит в основе обычного оператора float8 minus; но для любого другого подтипа необходимо преобразование некоторых типов. Кроме того, может потребоваться творческая мысль о том, как представить различия в виде чисел. В максимально возможной степени функция 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 может ускорять запросы с участием следующих операторов диапазона: =, &&, <@, @>, <<, >>, -|-, &< и &> (дополнительную информацию см. в разделе Функции диапазона и операторы).

Кроме того, B-дерев и хэш-индексы могут быть созданы для столбцов таблицы типов диапазона. Для этих типов индексов единственная полезная операция диапазона — это равенство. Для значений диапазона определен порядок сортировки 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")).

Расширение 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

Типы доменов

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

Например, мы могли бы создать домен над целыми числами, который принимает только положительные целые числа:

CREATE DOMAIN posint AS integer CHECK (VALUE > 0);
CREATE TABLE mytable (id posint);
INSERT INTO mytable VALUES(1);   -- works
INSERT INTO mytable VALUES(-1);  -- fails

Когда оператор или функция базового типа применяется к значению домена, домен автоматически преобразуется в базовый тип. Так, например, считается, что результат mytable.id - 1 имеет тип integer, а не posint. Мы могли бы написать (mytable.id - 1)::posint чтобы привести результат обратно к posint, в результате чего ограничения домена будут перепроверены. В этом случае это приведет к ошибке, если выражение было применено к значению идентификатора 1. Присвоение значения базового типа полю или переменной типа домена допускается без написания явного приведения, но ограничения домена будут проверены.

Для получения дополнительной информации см. CREATE DOMAIN.

Типы идентификаторов объектов

Идентификаторы объектов (OID) используются внутри QHB в качестве первичных ключей для различных системных таблиц. Тип oid представляет идентификатор объекта. Существует также несколько типов псевдонимов для oid: regproc, regprocedure, regoper, regoperator, regclass, regtype, regrole, regnamespace, regconfig и regdictionary. В Таблице 26 приведена общая информация по типам идентификаторов объектов.

Тип oid в настоящее время реализован как беззнаковое четырехбайтовое целое число. Следовательно, он недостаточно велик, чтобы обеспечить уникальность всей базы данных в больших базах данных или даже в больших отдельных таблицах.

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

Типы псевдонимов OID не имеют собственных операций, кроме специализированных процедур ввода и вывода. Эти подпрограммы могут принимать и отображать символические имена для системных объектов, а не исходное числовое значение, которое будет использовать тип oid. Типы псевдонимов позволяют упростить поиск значений OID для объектов. Например, чтобы проверить строки pg_attribute относящиеся к таблице mytable, можно написать:

SELECT * FROM pg_attribute WHERE attrelid = 'mytable'::regclass;

скорее, чем:

SELECT * FROM pg_attribute
  WHERE attrelid = (SELECT oid FROM pg_class WHERE relname = 'mytable');

Хотя это само по себе выглядит не так уж и плохо, все же упрощенно. Для выбора правильного OID потребуется гораздо более сложный суб-выбор, если в разных схемах есть несколько таблиц с именем mytable. Преобразователь ввода regclass обрабатывает поиск в таблице в соответствии с настройкой пути к схеме, поэтому автоматически выполняет «правильные действия». Точно так же приведение OID таблицы к regclass удобно для символического отображения числового OID.

Таблица 26. Типы идентификаторов объектов

Все типы псевдонимов OID для объектов, сгруппированных по пространству имен, принимают имена, соответствующие схеме, и будут отображать имена, соответствующие схеме, при выводе, если объект не будет найден в текущем пути поиска без проверки. Типы псевдонимов regproc и regoper будут принимать только входные имена, которые являются уникальными (не перегруженными), поэтому они имеют ограниченное использование; для большинства применений более подходящими являются regprocedure или regoperator. Для regoperator унарные операторы идентифицируются путем написания NONE для неиспользованного операнда.

Дополнительным свойством большинства типов псевдонимов OID является создание зависимостей. Если константа одного из этих типов появляется в сохраненном выражении (например, в выражении по умолчанию для столбца или в представлении), она создает зависимость от ссылочного объекта. Например, если столбец имеет выражение по умолчанию nextval(’my_seq’::regclass), QHB понимает, что выражение по умолчанию зависит от последовательности my_seq; система не позволит удалить последовательность без предварительного удаления выражения по умолчанию. regrole является единственным исключением для свойства. Константы этого типа не допускаются в таких выражениях.

Заметка
Типы псевдонимов OID не полностью соответствуют правилам изоляции транзакций. Планировщик также рассматривает их как простые константы, что может привести к неоптимальному планированию.

Другим типом идентификатора, используемым системой, является xid или идентификатор транзакции (сокращенно xact). Это тип данных системных столбцов xmin и xmax. Идентификаторы транзакции являются 32-битными величинами.

Третий тип идентификатора, используемый системой - cid, или идентификатор команды. Это тип данных системных столбцов cmin и cmax. Идентификаторы команд также являются 32-битными величинами.

Последний тип идентификатора, используемый системой — это tid, или идентификатор кортежа (идентификатор строки). Это тип данных системного столбца ctid. Идентификатор кортежа — это пара (номер блока, индекс кортежа в блоке), который идентифицирует физическое местоположение строки в своей таблице.

(Системные столбцы более подробно описаны в разделе Системные столбцы).

Тип pg_lsn

Тип данных pg_lsn может использоваться для хранения данных LSN (порядкового номера журнала - Log Sequence Number), которые являются указателем на местоположение в WAL. Этот тип является представлением XLogRecPtr и внутренним системным типом QHB.

Внутренне LSN представляет собой 64-разрядное целое число, представляющее позицию байта в потоке журнала упреждающей записи. Он печатается в виде двух шестнадцатеричных чисел длиной до 8 цифр, разделенных косой чертой; например, 16/B374D848. Тип pg_lsn поддерживает стандартные операторы сравнения, такие как = и >. Два номера LSN могут быть вычтены с помощью оператора -; результат - число байтов, разделяющих эти местоположения в wal-журнале.

Псевдо-типы

Система типов QHB содержит ряд записей специального назначения, которые в совокупности называются псевдотипами. Псевдотип нельзя использовать в качестве типа данных столбца, но его можно использовать для объявления аргумента функции или типа результата. Каждый из доступных псевдотипов полезен в ситуациях, когда поведение функции не соответствует простому взятию или возвращению значения определенного типа данных SQL. В таблице 7.27 перечислены существующие псевдотипы.

Таблица 27. Псевдо-типы

Функции, закодированные в C/Rust (встроенные или динамически загружаемые), могут быть объявлены для принятия или возврата любого из этих псевдо-типов данных. Автор функции должен гарантировать, что функция будет вести себя безопасно, когда псевдотип используется в качестве типа аргумента.

Функции, закодированные в процедурных языках, могут использовать псевдотипы только в соответствии с их языками реализации. В настоящее время большинство процедурных языков запрещают использование псевдотипа в качестве типа аргумента и разрешают только void и record в качестве типа результата (плюс trigger или event_trigger когда функция используется в качестве триггера или триггера события). Некоторые также поддерживают полиморфные функции, используя типы anyelement, anyarray, anynonarray, anyenum и anyrange.

Псевдотип internal используется для объявления функций, предназначенных только для внутреннего вызова системой базы данных, а не путем прямого вызова в запросе SQL. Если у функции есть хотя бы один аргумент типа internal, ее нельзя вызвать из SQL. Чтобы сохранить безопасность типов этого ограничения, важно следовать этому правилу кодирования: не создавайте никакую функцию, которая объявляется возвращающей internal если у нее нет хотя бы одного internal аргумента.