Типы данных PostgreSQL

В этой главе мы рассмотрим типы данных PostgreSQL. Типы данных — это то, что мы задаём для каждого поля при создании таблицы.

Преимущества задания типов данных:

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


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

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

В следующей таблице перечислены доступные числовые типы.

NameРазмер храненияОписаниеДиапазон
smallint2 байтаЦелое число с малым диапазономот -32768 до +32767
целое число4 байтаЧасто используемое целое числоот -2147483648 до +2147483647
bigint8 байтЦелое число с большим диапазономот -9223372036854775808 до +9223372036854775807
decimalПеременная длинаЗадаваемая пользователем точность, точное131072 цифры до десятичной точки; 16383 цифры после десятичной точки
numericПеременная длинаЗадаваемая пользователем точность, точное131072 цифры до десятичной точки; 16383 цифры после десятичной точки
real4 байтаПеременная точность, неточноеточность 6 десятичных цифр
double precision8 байтПеременная точность, неточноеточность 15 десятичных цифр
smallserial2 байтаМалое целое с автоувеличениемот 1 до 32767
serial4 байтаЦелое с автоувеличениемот 1 до 2147483647
bigserial8 байтБольшое автоинкрементное целоеот 1 до 9223372036854775807

Денежные типы

Тип money хранит денежные суммы с фиксированной дробной точностью.

Значения типов numeric, int и bigint можно преобразовать в money. Использование чисел с плавающей запятой для работы с денежными типами не рекомендуется из-за возможных ошибок округления.

NameРазмер храненияОписаниеДиапазон
money8 байтДенежная суммаот -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 zone8 байтДата и время с часовым поясом4713 до н. э.294276 н. э.1 millisecond / 14 цифр
date4 байтаТолько дата4713 до н. э.5874897 н. э.1 day
time [ (p) ] [ without time zone ]8 байтТолько время суток00:00:0024:00:001 millisecond / 14 цифр
time [ (p) ] with time zone12 bytesТолько время суток с часовым поясом00:00:00+145924:00:00-14591 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Размер храненияОписаниеПредставление
point16 байтовТочка на плоскости(x,y)
line32 байта(Бесконечная) прямая (не полностью реализована)((x1,y1),(x2,y2))
lseg32 байта(Конечный) отрезок((x1,y1),(x2,y2))
⟦0⟧32 байтаПрямоугольник((x1,y1),(x2,y2))
path16+16n байтЗамкнутый путь (похож на многоугольник)((x1,y1),...)
path16+16n байтОткрытый путь[(x1,y1),...]
многоугольник40+16n байтМногоугольник (аналогичен замкнутому пути)((x1,y1),...)
circle24 байтаcircle<(x,y),r> (центр и радиус)

Сетевые типы адресов

PostgreSQL предоставляет типы данных для хранения адресов IPv4, IPv6 и MAC.

Хранить сетевые адреса с помощью этих типов данных лучше, чем использовать обычные текстовые типы, потому что эти типы обеспечивают проверку ошибок ввода и специальные операции и функции.

NameРазмер храненияОписание
cidr7 или 19 байтСеть IPv4 или IPv6
inet7 или 19 байтIPv4- или IPv6-хост и сеть
macaddr6 байт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СсылкаОписаниеПример
oidanyчисловой идентификатор объекта564182
regprocpg_procимя функцииsum
regprocedurepg_procфункция с типами аргументовsum(int4)
regoperpg_operatorимя оператора+
regoperatorpg_operatorоператор с типами аргументов*(integer,integer) или -(NONE,integer)
regclasspg_classимя отношенияpg_type
regtypepg_typeимя типа данныхцелое число
regconfigpg_ts_configконфигурация текстового поискаанглийский
regdictionarypg_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

Другие расширения