Типы данных PostgreSQL: таблица и примеры

Для целых чисел в PostgreSQL используют integer или bigint, для точных сумм — numeric(p,s), для строк без ограничения длины — text. Календарную дату храните в date, момент события — в timestamptz. Ниже — таблица выбора и примеры, после которых приведён справочник остальных типов.

Как выбрать тип данных PostgreSQL

Задача	Тип	Что проверить
Количество товаров	integer (int, int4)	Диапазон от −2 147 483 648 до 2 147 483 647; для больших значений — bigint
Сумма заказа	numeric(12,2)	12 цифр всего, 2 после запятой; больше 10 цифр до запятой не поместится
Идентификатор	bigint GENERATED ALWAYS AS IDENTITY	Генерация значения не заменяет PRIMARY KEY
Внешний идентификатор	uuid	Тип хранения UUID; способ генерации выбирается отдельно
Название и описание	text / varchar(n)	varchar(n) ограничивает число символов; text не задаёт такой границы
Дата документа	date	Без времени суток
Момент события	timestamptz	Отображение зависит от TimeZone сеанса; исходное название часового пояса не хранится
Локальное время по расписанию	timestamp without time zone	Не определяет однозначный момент без часового пояса
Гибкие атрибуты	jsonb	Пригоден для поиска по структуре; json сохраняет исходное текстовое представление
Признак / двоичные данные	boolean / bytea	NULL отличается от false; двоичные данные отличаются от текста

numeric: точность и масштаб на примере

numeric и decimal — синонимы. При приведении к numeric(12,2) лишние дробные разряды округляются. Для финансовых сумм это обычно понятнее, чем приближённая арифметика real и double precision.

SELECT 19.995::numeric(12,2) AS amount;
-- 20.00

serial и identity: что выбрать для нового столбца

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

CREATE TEMP TABLE demo_orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  amount numeric(12,2) NOT NULL CHECK (amount >= 0),
  customer_name text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  attributes jsonb NOT NULL DEFAULT '{}'::jsonb
);
INSERT INTO demo_orders (amount, customer_name)
VALUES (19.995, 'Анна');
SELECT id, amount FROM demo_orders;
-- 1 | 20.00

Следующий шаг — спроектировать ограничения и индексы под ваши запросы. Темы программы собраны в курсе по PostgreSQL.

Документация

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

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

Наименование	Размер	Описание	Диапазон
smallint	2 байта	Целое число малого диапазона	-32768 до +32767
integer	4 байта	Типичный выбор для целого числа	-2147483648 до +2147483647
bigint	8 байт	Целое число большого диапазона	-9223372036854775808 до 9223372036854775807
decimal	переменная	Указанное пользователем значение , точное	До 131072 цифр перед запятой; до 16383 цифр после запятой
numeric	переменная	Указанное пользователем значение, точное	до 131072 цифр до запятой; до 16383 цифр после запятой
real	4 байта	variable-precision,inexact	Точность 6 десятичных цифр
double precision	8 байт	Переменная точность, неточная	Точность 15 десятичных цифр
smallserial	2 байта	Небольшое автоинкрементное целое число	1 до 32767
serial	4 байта	Автоинкрементное целое число	1 до 2147483647
bigserial	8 байт	Большое автоинкрементное число	1 до 9223372036854775807

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

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

Наименование	Размер	Описание	Диапазон
money	8 байт	Сумма в валюте	-92233720368547758.08 до +92233720368547758.07

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

В таблице ниже приведены символьные типы, доступные в PostgreSQL.

S. No.	Наименование и Описание
1	character varying(n), varchar(n) текст с ограничением по длине (максимальная длина строка может быть ограничена)
2	character(n), char(n) текст фиксированной длины (строка всегда имеет строго заданный размер)
3	text текст неограниченной длины

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

Тип данных bytea делает возможным хранение двоичных строк.

Наименование	Размер	Описание
bytea	1 или 4 байта плюс сама двоичная строка	Двоичная строка переменной длины

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

PostgreSQL поддерживает полный набор типов дат и времени SQL. Даты считаются по Григорианскому календарю. Здесь все типы имеют разрешение 1 микросекунда / 14 цифр, кроме типа даты, разрешение которого – день.

Наименование	Размер	Описание	Нижнее значение	Верхнее значение
timestamp [(p)] [без часового пояса ]	8 байт	Дата и время (без часового пояса)	4713 до н.э.	294276 н.э.
TIMESTAMPTZ	8 байт	Дата и время (с часовым поясом)	4713 до н.э.	294276 н.э.
date	4 байта	Дата (без времени суток)	4713 до н.э.	5874897 н.э.
time [ (p)] [ без часового пояса ]	8 байт	Время суток (без даты)	00:00:00	24:00:00
time [ (p)] с часовым поясом	12 байт	Только время суток, с часовым поясом	00:00:00+1459	24:00:00-1459
interval [fields ] [(p) ]	12 байт	Временной интервал	-178000000 лет	178000000 лет

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

В PostgreSQL есть стандартный логический тип данных. Данный тип может иметь следующие состояния: "true", "false" и третье состояние, "unknown", которое представляется SQL - значением NULL.

Наименование	Размер	Описание
boolean	1 байт	Истина или ложь

Типы перечислений

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

В отличие от других типов, данный тип должен быть создан при помощи команды CREATE TYPE. Этот тип данных используется для хранения статического упорядоченного набора значений. Например, направления по компасу: СЕВЕР, ЮГ, ВОСТОК и ЗАПАД или дни недели, как показано ниже:

CREATE TYPE week AS ENUM ('Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', 'Sun');

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

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

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

Наименование	Размер	Представление	Описание
point	16 байт	Точка на плоскости	(x,y)
line	32 байта	Бесконечная прямая	((x1,y1),(x2,y2))
lseg	32 байта	Ограниченный сегмент линии (отрезок)	((x1,y1),(x2,y2))
box	32 байта	Прямоугольная коробка	((x1,y1),(x2,y2))
path	16+16n байт	Закрытый путь (подобный многоугольнику)	((x1,y1),...)
path	16+16n байт	Открытый путь	[(x1,y1),...]
polygon	40+16n	Многоугольник (подобный закрытому пути)	((x1,y1),...)
circle	24 bytes	Окружность	<(x,y),r> (центр окружности и радиус)

Типы, описывающие сетевые адреса

PostgreSQL предлагает типы данных для хранения адресов IPv4, IPv6 и MAC. Для хранения сетевых адресов лучше использовать эти типы, а не простые текстовые строки, так как PostgreSQL проверяет вводимые значения данных типов и предоставляет специализированные операторы и функции для работы с ними.

Наименование	Размер	Описание
cidr	7 или 19 байт	Сети IPv4 и IPv6
inet	7 или 19 байт	Узлы и сети IPv4 и IPv6
macaddr	6 байт	MAC- адреса

Битовые строки

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

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

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

S. No.

Наименование и описание

tsvector

Значение данного типа содержит отсортированный список неповторяющихся лексем, т. е. слов, нормализованных так, что все словоформы сводятся к одной.

tsquery

Данное значение содержит искомые лексемы, объединяемые логическими операторами (AND), | (OR) и ! (NOT). Для группировки операторов могут использоваться скобки.

Тип UUID

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

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

Пример UUID в этом стандартном виде: 550e8400-e29b-41d4-a716-446655440000

Тип XML

Тип XML предназначен для хранения XML-данных. Для начала необходимо создать XML значения, использую функцию xmlparse следующим образом:

XMLPARSE (DOCUMENT '<?xml version="1.0"?>
<tutorial>
<title>PostgreSQL Tutorial </title>
   <topics>...</topics>
</tutorial>')
 

XMLPARSE (CONTENT 'xyz<foo>bar</foo><bar>foo</bar>')

Тип JSON

Типы JSON предназначены для хранения данных JSON (JavaScript Object Notation). Такие данные можно хранить и в типе text, но типы 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 monthly_savings (
   name text,
   saving_per_quarter integer[],
   scheme text[][]
);

Или при помощи использования ключевого слова "ARRAY":

CREATE TABLE monthly_savings (
   name text,
   saving_per_quarter integer ARRAY[4],
   scheme text[][]
);

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

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

INSERT INTO monthly_savings
VALUES (‘Manisha’, 
‘{20000, 14600, 23500, 13250}’, 
‘{{“FD”, “MF”}, {“FD”, “Property”}}’);

Обращение к массивам

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

SELECT name FROM monhly_savings WHERE saving_per_quarter[2] > saving_per_quarter[4];

Изменение массивов

Пример изменения массивов приведен ниже:

UPDATE monthly_savings SET saving_per_quarter = '{25000,25000,27000,27000}'
WHERE name = 'Manisha';

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

UPDATE monthly_savings SET saving_per_quarter = ARRAY[25000,25000,27000,27000]
WHERE name = 'Manisha';

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

Пример поиска массивов приведен ниже:

SELECT * FROM monthly_savings WHERE saving_per_quarter[1] = 10000 OR
saving_per_quarter[2] = 10000 OR
saving_per_quarter[3] = 10000 OR
saving_per_quarter[4] = 10000;

Если размер массива известен, метод поиска, указанный выше, приемлем. В случае если размер массива не известен, используйте следующую команду:

SELECT * FROM monthly_savings WHERE 10000 = ANY (saving_per_quarter);

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

Составной тип представляет структуру табличной строки или записи; по сути это просто список имён полей и соответствующих типов данных.

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

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

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

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

CREATE TABLE on_hand (
   item inventory_item,
   count integer
);

Ввод составного значения

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

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

Это действительно для inventory_item, определенного выше. Ключевое слово ROW на самом деле является необязательным, если у Вас более одного поля в выражении.

Обращение к составным типам

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

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;

Диапазонные типы

Диапазонные типы представляют диапазоны значений некоторого типа данных. Тип диапазона может быть дискретным (например, все целые значения от 1 до 10) либо непрерывным (например, любой момент времени между 10:00 и 11:00).

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

int4range − диапазон подтипа integer
int8range − диапазон подтипа bigint
numrange − диапазон подтипа numeric
tsrange − диапазон подтипа timestamp без часового пояса
tstzrange − диапазон подтипа timestamp с часовым поясом
daterange − диапазон подтипа date

Также могут быть созданы пользовательские типы диапазонов, такие как диапазоны IP-адресов, использующие тип inet в качестве базы, или диапазоны с плавающей запятой, использующие тип данных float в качестве базы.

Типы диапазонов поддерживают включающие и исключающие границы диапазона с использованием символов [ ] и ( ) соответственно. Например, «[4,9)» представляет все целые числа, начиная с 4 включительно и заканчивая 9, но, не включая его.

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

Идентификаторы объектов (OID) используются внутри PostgreSQL в качестве первичных ключей для различных системных таблиц. Если указано WITH OIDS или включена конфигурационная переменная default_with_oids, то только в таких случаях OID добавляются в пользовательские таблицы. В следующей таблице перечислены несколько типов псевдонимов. Псевдонимы OID не имеют собственных операций, за исключением специализированных процедур ввода и вывода.

Наименование	Ссылки	Описание	Пример значения
oid	any	числовой идентификатор объекта	564182
regproc	pg_proc	имя функции	sum
regprocedure	pg_proc	функция с типами аргументов	sum(int4)
regoper	pg_operator	имя оператора	+
regoperator	pg_operator	оператор с типами аргументов	*(integer,integer) or -(NONE,integer)
regclass	pg_class	имя отношения	pg_type
regtype	pg_type	имя типа данных	integer
regconfig	pg_ts_config	имя роли	English
regdictionary	pg_ts_dict	пространство имён	simple

Псевдотипы

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

Все существующие псевдотипы перечислены ниже:

S. No.	Наименование и описание
1	any Указывает, что функция принимает любой вводимый тип данных.
2	anyelement Указывает, что функция принимает любой тип данных
3	anyarray Указывает, что функция принимает любой тип массива
4	anynonarray Указывает, что функция принимает любой тип данных, кроме массивов
5	anyenum Указывает, что функция принимает любое перечисление
6	anyrange Указывает, что функция принимает любой диапазонный тип данных
7	cstring Указывает, что функция принимает или возвращает строку в стиле C.
8	internal Указывает, что функция принимает или возвращает внутренний серверный тип данных.
9	language_handler Обработчик процедурного языка объявляется как возвращающий тип `language_handler`
10	fdw_handler Обработчик обёртки сторонних данных объявляется как возвращающий тип `fdw_handler`.
11	record Указывает, что функция принимает или возвращает неопределённый тип строки.
12	trigger Триггерная функция объявляется как возвращающая тип `trigger`.
13	void Указывает, что функция не возвращает значение.

← Предыдущая статья

PostgreSQL - Синтаксис

Следующая статья →

PostgreSQL – Создание базы данных