Типы данных PostgreSQL
В этой главе мы рассмотрим типы данных PostgreSQL. Типы данных — это то, что мы задаём для каждого поля при создании таблицы.
Преимущества задания типов данных:
PostgreSQL предоставляет богатый набор типов данных. Пользователи могут использовать команду CREATE TYPE для создания новых типов данных в базе данных. PostgreSQL имеет множество типов данных, и ниже мы подробно их объясним.
Числовые типы
Числовые типы состоят из двух-, четырёх- или восьмибайтовых целых чисел, четырёх- или восьмибайтовых чисел с плавающей точкой и десятичных чисел с выбираемой точностью.
В следующей таблице перечислены доступные числовые типы.
| Name | Размер хранения | Описание | Диапазон |
|---|---|---|---|
| smallint | 2 байта | Целое число с малым диапазоном | от -32768 до +32767 |
| целое число | 4 байта | Часто используемое целое число | от -2147483648 до +2147483647 |
| bigint | 8 байт | Целое число с большим диапазоном | от -9223372036854775808 до +9223372036854775807 |
| decimal | Переменная длина | Задаваемая пользователем точность, точное | 131072 цифры до десятичной точки; 16383 цифры после десятичной точки |
| numeric | Переменная длина | Задаваемая пользователем точность, точное | 131072 цифры до десятичной точки; 16383 цифры после десятичной точки |
| real | 4 байта | Переменная точность, неточное | точность 6 десятичных цифр |
| double precision | 8 байт | Переменная точность, неточное | точность 15 десятичных цифр |
| smallserial | 2 байта | Малое целое с автоувеличением | от 1 до 32767 |
| serial | 4 байта | Целое с автоувеличением | от 1 до 2147483647 |
| bigserial | 8 байт | Большое автоинкрементное целое | от 1 до 9223372036854775807 |
Денежные типы
Тип money хранит денежные суммы с фиксированной дробной точностью.
Значения типов numeric, int и bigint можно преобразовать в money. Использование чисел с плавающей запятой для работы с денежными типами не рекомендуется из-за возможных ошибок округления.
| Name | Размер хранения | Описание | Диапазон |
|---|---|---|---|
| money | 8 байт | Денежная сумма | от -92233720368547758.08 до +92233720368547758.07 |
Символьные типы
В следующей таблице перечислены символьные типы, поддерживаемые PostgreSQL:
| Нет. | Имя и описание |
|---|---|
| 1 | character varying(n), varchar(n) Переменная длина с ограничением |
| 2 | character(n), char(n) Фиксированная длина, дополняется пробелами |
| 3 | text Переменная длина, без ограничения длины |
Типы даты/времени
В следующей таблице перечислены типы даты и времени, поддерживаемые PostgreSQL.
| Name | Размер хранения | Описание | Наименьшее значение | Наибольшее значение | Разрешение |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 байт | Дата и время (без часового пояса) | 4713 до н. э. | 294276 н. э. | 1 millisecond / 14 цифр |
| timestamp [ (p) ] with time zone | 8 байт | Дата и время с часовым поясом | 4713 до н. э. | 294276 н. э. | 1 millisecond / 14 цифр |
| date | 4 байта | Только дата | 4713 до н. э. | 5874897 н. э. | 1 day |
| time [ (p) ] [ without time zone ] | 8 байт | Только время суток | 00:00:00 | 24:00:00 | 1 millisecond / 14 цифр |
| time [ (p) ] with time zone | 12 bytes | Только время суток с часовым поясом | 00:00:00+1459 | 24:00:00-1459 | 1 millisecond / 14 цифр |
| interval [ fields ] [ (p) ] | 12 bytes | Интервал времени | -178000000 лет | 178000000 лет | 1 millisecond / 14 цифр |
Логический тип
PostgreSQL поддерживает стандартный логический тип данных.
Логический тип имеет два состояния: "true" (истина) или "false" (ложь), а также третье состояние "unknown" (неизвестное), представленное NULL.
| Name | Формат хранения | Описание |
|---|---|---|
| логическое значение | 1 байт | true/false |
Перечисляемые типы
Перечисляемый тип — это тип данных, содержащий упорядоченный набор статических значений.
Перечисляемые типы в PostgreSQL похожи на типы enum в языке C.
В отличие от других типов, перечисляемые типы необходимо создавать с помощью команды CREATE TYPE.
CREATE TYPE mood AS ENUM ('sad', 'ok', 'happy');
Создайте дни недели, как показано ниже:
CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');
Как и другие типы, после создания перечисляемые типы можно использовать в определениях таблиц и функций.
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)
Геометрические типы
Геометрические типы данных представляют двумерные плоские объекты.
В следующей таблице перечислены геометрические типы, поддерживаемые PostgreSQL.
Самый базовый тип: point. Он лежит в основе остальных типов.
| Name | Размер хранения | Описание | Представление |
|---|---|---|---|
| point | 16 байтов | Точка на плоскости | (x,y) |
| line | 32 байта | (Бесконечная) прямая (не полностью реализована) | ((x1,y1),(x2,y2)) |
| lseg | 32 байта | (Конечный) отрезок | ((x1,y1),(x2,y2)) |
| ⟦0⟧ | 32 байта | Прямоугольник | ((x1,y1),(x2,y2)) |
| path | 16+16n байт | Замкнутый путь (похож на многоугольник) | ((x1,y1),...) |
| path | 16+16n байт | Открытый путь | [(x1,y1),...] |
| многоугольник | 40+16n байт | Многоугольник (аналогичен замкнутому пути) | ((x1,y1),...) |
| circle | 24 байта | circle | <(x,y),r> (центр и радиус) |
Сетевые типы адресов
PostgreSQL предоставляет типы данных для хранения адресов IPv4, IPv6 и MAC.
Хранить сетевые адреса с помощью этих типов данных лучше, чем использовать обычные текстовые типы, потому что эти типы обеспечивают проверку ошибок ввода и специальные операции и функции.
| Name | Размер хранения | Описание |
|---|---|---|
| cidr | 7 или 19 байт | Сеть IPv4 или IPv6 |
| inet | 7 или 19 байт | IPv4- или IPv6-хост и сеть |
| macaddr | 6 байт | MAC-адрес |
При сортировке типов данных inet или cidr адреса IPv4 всегда сортируются перед адресами IPv6, включая IPv4-адреса, инкапсулированные или отображённые внутри IPv6-адресов, например ::10.2.3.4 или ::ffff:10.4.3.2.
Типы битовых строк
Битовая строка — это строка из единиц и нулей. Они могут использоваться для хранения и визуализации битовых масок. У нас есть два SQL-типа битовых строк: bit(n) и bit varying(n), где n — положительное целое число.
Данные типа bit должны точно соответствовать длине n; попытка сохранить данные меньшей или большей длины является ошибкой. Данные типа bit varying имеют переменную длину с максимальной длиной n; более длинные строки будут отклонены. Запись bit без длины эквивалентна bit(1), а bit varying без длины означает отсутствие ограничения длины.
Типы текстового поиска
Полнотекстовый поиск — это поиск документов из коллекции документов на естественном языке, соответствующих запросу.
PostgreSQL предоставляет два типа данных для поддержки полнотекстового поиска:
| Number | Имя и описание |
|---|---|
| 1 | tsvector Значение tsvector — это отсортированный список различных лексем, то есть нормализованных форм различных вариантов одного и того же слова. |
| 2 | tsquery tsquery хранит лексемы, используемые для поиска, и объединяет их с помощью логических операторов & (AND), | (OR) и ! (NOT); круглые скобки используются для принудительной группировки операторов. |
Тип UUID
Тип данных uuid используется для хранения универсально уникальных идентификаторов (UUID), как определено в RFC 4122, ISO/IEF 9834-8:2005 и связанных стандартах. (В некоторых системах этот тип данных называют глобально уникальным идентификатором, или GUID.) Этот идентификатор представляет собой 128-битный идентификатор, создаваемый алгоритмом, что делает невозможным его совпадение с идентификаторами, созданными другими способами, среди модулей, о которых известно, что они используют тот же алгоритм. Поэтому для распределённых систем этот идентификатор обеспечивает лучшую гарантию уникальности, чем последовательности, поскольку последовательности могут гарантировать уникальность только в одной базе данных.
UUID записываются как последовательность шестнадцатеричных цифр в нижнем регистре, разделённых на группы дефисами, а именно группа из 8 цифр плюс 3 группы по 4 цифры плюс группа из 12 цифр, всего 32 цифры, представляющие 128 бит. Пример такого стандартного UUID выглядит следующим образом:
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
Тип XML
Тип данных xml можно использовать для хранения XML-данных. Его преимущество перед хранением XML-данных в типе text состоит в том, что он может проверять входные значения на правильную сформированность, а также поддерживает функции для их типобезопасной проверки. Чтобы использовать этот тип данных, необходимо включить его на этапе компиляции.configure --with-libxml。
xml может хранить правильно сформированные «документы», определённые стандартом XML, а также определённые в стандарте XML с помощьюXMLDecl? contentфрагменты «content»; грубо говоря, это означает, что фрагменты content могут иметь несколько элементов верхнего уровня или символьных узлов. Выражение xml value IS DOCUMENT можно использовать для определения, является ли конкретное значение xml полным документом или фрагментом content.
Создание значений XML
Используйте функцию xmlparse: для создания значений типа xml из символьных данных:
XMLPARSE (DOCUMENT '<?xml version="1.0"?><book><title>Manual</title><chapter>...</chapter></book>') XMLPARSE (CONTENT 'abc<foo>bar</foo><bar>foo</bar>')
Типы JSON
Тип данных json можно использовать для хранения данных JSON (JavaScript Object Notation). Такие данные также можно хранить как текст, но тип данных json более удобен для проверки того, что каждое сохранённое значение является допустимым значением JSON.
Кроме того, существуют связанные функции для обработки данных json:
| Пример | Пример результата |
|---|---|
| array_to_json('{{1,5},{99,100}}'::int[]) | [[1,5],[99,100]] |
| row_to_json(row(1,'foo')) | {"f1":1,"f2":"foo"} |
Типы массивов
PostgreSQL позволяет определять поля как многомерные массивы переменной длины.
Типы массивов могут быть любым базовым типом, пользовательским типом, типом перечисления или составным типом.
Объявление массивов
При создании таблицы мы можем объявлять массивы следующим образом:
CREATE TABLE sal_emp (
name text,
pay_by_quarter integer[],
schedule text[][]
);
pay_by_quarter — одномерный целочисленный массив, а schedule — двумерный текстовый массив.
Мы также можем использовать ключевое слово "ARRAY", как показано ниже:
CREATE TABLE sal_emp ( name text, pay_by_quarter integer ARRAY[4], schedule text[][] );
Вставка значений
Чтобы вставить значения, используйте фигурные скобки {}, а элементы внутри {} разделяются запятыми:
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 name FROM sal_emp WHERE pay_by_quarter[1] <> pay_by_quarter[2]; name ------- Carol (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';
Поиск в массивах
Чтобы найти значение в массиве, необходимо проверить каждое значение массива.
Например:
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;
В качестве альтернативы можно использовать следующий оператор, чтобы найти строки, в которых все значения элементов массива равны 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;
Составные типы
Составной тип представляет структуру строки или записи; фактически это просто список имен полей и их типов данных. PostgreSQL позволяет использовать составные типы так же, как простые типы данных. Например, поле таблицы можно объявить как составной тип.
Объявление составных типов
Ниже приведены два простых примера определения составных типов:
CREATE TYPE complex AS (
r double precision,
i double precision
);
CREATE TYPE inventory_item AS (
name text,
supplier_id integer,
price numeric
);
Синтаксис похож на CREATE TABLE, за исключением того, что здесь можно объявить только имена и типы полей.
После определения типов мы можем использовать их для создания таблиц:
CREATE TABLE on_hand (
item inventory_item,
count integer
);
INSERT INTO on_hand VALUES (ROW('fuzzy dice', 42, 1.99), 1000);
Ввод значений составного типа
Чтобы записать значение составного типа в виде текстовой константы, заключите значения полей в круглые скобки и разделите их запятыми. Вы можете заключить любое значение поля в двойные кавычки; если само значение содержит запятые или круглые скобки, его необходимо заключить в двойные кавычки.
Общий формат константы составного типа выглядит следующим образом:
'( val1 , val2 , ... )'
Пример:
'("fuzzy dice",42,1.99)'
Доступ к составным типам
Чтобы обратиться к полю столбца составного типа, мы пишем точку и имя поля, почти так же, как при выборе поля из имени таблицы. Фактически, поскольку это так похоже на выбор поля из имени таблицы, нам часто нужно использовать круглые скобки, чтобы не запутать парсер. Например, вам может потребоваться выбрать некоторые подполя из примерной таблицы on_hand, вот так:
SELECT item.name FROM on_hand WHERE item.price > 9.99;
Это не сработает, потому что согласно синтаксису SQL item выбирается из имени таблицы, а не из имени поля. Нужно написать так:
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, и затем из него можно выбирать подполя.
Диапазонные типы
Диапазонные типы данных представляют определённый диапазон значений некоторого элементного типа.
Например, диапазон timestamp может использоваться для представления временного диапазона, в течение которого переговорная комната забронирована.
Встроенные диапазонные типы в PostgreSQL:
int4range — диапазон integer
int8range — диапазон bigint
numrange — диапазон numeric
tsrange — диапазон timestamp без часового пояса
tstzrange — диапазон timestamp с часовым поясом
daterange — диапазон date
Кроме того, вы можете определять собственные диапазонные типы.
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
(1108, '[2010-01-01 14:30, 2010-01-01 15:30)');
-- 包含
SELECT int4range(10, 20) @> 3;
-- 重叠
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);
-- 提取上边界
SELECT upper(int8range(15, 25));
-- 计算交叉
SELECT int4range(10, 20) * int4range(15, 25);
-- 范围是否为空
SELECT isempty(numrange(1, 5));
Ввод значений диапазона должен соответствовать следующему формату:
(lower bound, upper bound) (lower bound, upper bound] [lower bound, upper bound) [lower bound, upper bound] empty
Круглые или квадратные скобки указывают, являются ли нижняя и верхняя границы исключающими или включающими. Обратите внимание, что последний формат пуст и представляет пустой диапазон (диапазон, не содержащий значений).
-- 包括3,不包括7,并且包括二者之间的所有点 SELECT '[3,7)'::int4range; -- 不包括3和7,但是包括二者之间所有点 SELECT '(3,7)'::int4range; -- 只包括单一值4 SELECT '[4,4]'::int4range; -- 不包括点(被标准化为‘空’) SELECT '[4,4)'::int4range;
Типы идентификаторов объектов
PostgreSQL внутри себя использует идентификаторы объектов (OID) в качестве первичных ключей для различных системных таблиц.
При этом система не добавляет системное поле OID в создаваемые пользователем таблицы (если только при создании таблицы не указано WITH OIDS или параметр конфигурации default_with_oids не установлен в on). Тип oid представляет идентификатор объекта. Кроме того, oid имеет несколько псевдонимов: regproc, regprocedure, regoper, regoperator, regclass, regtype, regconfig и regdictionary.
| Name | Ссылка | Описание | Пример |
|---|---|---|---|
| oid | any | числовой идентификатор объекта | 564182 |
| regproc | pg_proc | имя функции | sum |
| regprocedure | pg_proc | функция с типами аргументов | sum(int4) |
| regoper | pg_operator | имя оператора | + |
| regoperator | pg_operator | оператор с типами аргументов | *(integer,integer) или -(NONE,integer) |
| regclass | pg_class | имя отношения | pg_type |
| regtype | pg_type | имя типа данных | целое число |
| regconfig | pg_ts_config | конфигурация текстового поиска | английский |
| regdictionary | pg_ts_dict | словарь текстового поиска | простой |
Псевдотипы
Система типов PostgreSQL содержит ряд элементов специального назначения, которые в совокупности называются псевдотипами. Псевдотипы нельзя использовать в качестве типов данных полей, но их можно использовать для объявления типа аргумента или результата функции. Псевдотипы полезны, когда функция не просто принимает и возвращает определённый тип данных SQL.
В следующей таблице перечислены все псевдотипы:
| Name | Описание |
|---|---|
| any | Указывает, что функция принимает любой тип входных данных. |
| anyelement | Указывает, что функция принимает любой тип данных. |
| anyarray | Указывает, что функция принимает любой тип данных массива. |
| anynonarray | Указывает, что функция принимает любой тип данных, не являющийся массивом. |
| anyenum | Указывает, что функция принимает любой перечисляемый тип данных. |
| anyrange | Указывает, что функция принимает любой диапазонный тип данных. |
| cstring | Указывает, что функция принимает или возвращает C-строку с завершающим нулём. |
| internal | Указывает, что функция принимает или возвращает внутренний тип данных сервера. |
| language_handler | Обработчик вызова процедурного языка объявлен возвращающим language_handler. |
| fdw_handler | Обёртка сторонних данных объявлена возвращающей fdw_handler. |
| record | Идентифицирует функцию как возвращающую неопределённый тип строки. |
| trigger | Триггерная функция объявлена возвращающей trigger. |
| void | Указывает, что функция не возвращает значение. |
| opaque | Устаревший тип, ранее использовавшийся для всех вышеперечисленных целей. |
Другие расширенияДополнительную информацию см. в:Типы данных PostgreSQL