Типы данных PostgreSQL

Тип данных определяет, какие значения можно хранить в столбце и какие операции с ними доступны. Ниже собраны основные типы PostgreSQL, примеры их применения и особенности, которые стоит учитывать при создании таблиц.

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

Целые числа

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

Обычно достаточно integer. Если значения могут выйти за его диапазон, выбирайте bigint.

Числа с дробной частью

В обычной записи numeric(p, s) параметр p задаёт общее количество цифр, а s — количество цифр после точки. Например, numeric(10, 2) допускает восемь цифр до точки и две после неё. Лишние дробные цифры округляются; превышение допустимой целой части вызывает ошибку.

numeric без параметров не задаёт фиксированную точность и масштаб.

Для денежных сумм обычно выбирают numeric: real и double precision могут давать погрешность при хранении и вычислениях.

Автоматическая нумерация: IDENTITY и SERIAL

Для автоматически создаваемого числового идентификатора можно использовать столбец IDENTITY:

sql
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY

PostgreSQL получает значения из последовательности. ALWAYS ограничивает обычную вставку явно заданного идентификатора; вариант BY DEFAULT позволяет указать его вручную.

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

Ни SERIAL, ни IDENTITY сами по себе не гарантируют уникальность. Для неё нужны PRIMARY KEY или UNIQUE. В нумерации возможны пропуски, например после отмены транзакции.

Строковые типы

varchar без параметра длины, как и text, не задаёт ограничения на число символов. При этом технический предел отдельного строкового значения составляет около 1 ГБ.

Параметр n измеряется в символах, а не в байтах. Обычно для текста выбирают text, а varchar(n) используют, если ограничение длины связано с требованиями к данным. char(n) не даёт преимущества в скорости.

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

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

boolean, или bool, хранит TRUE и FALSE. Если столбец допускает отсутствие значения, в нём также может находиться NULL.

Пример определения столбца:

sql
is_active boolean NOT NULL DEFAULT TRUE

В SQL удобнее писать TRUE и FALSE без кавычек. PostgreSQL также распознаёт строковые формы, например 'yes', 'no', '1' и '0', когда ожидается логическое значение.

NULL не равен FALSE. Клиентская программа может показывать его как пустую ячейку, но это не пробел и не пустая строка.

Дата и время

timestamp — сокращение для timestamp without time zone, а timestamptz — для timestamp with time zone.

TIMESTAMPTZ не сохраняет исходное название часового пояса. Значение хранится как момент времени в UTC, а при выводе отображается в часовом поясе текущей сессии. Если нужно сохранить именно Europe/Moscow, для него потребуется отдельный столбец.

Для времени создания записи или оплаты обычно подходит timestamptz. Для даты рождения — date, для времени открытия магазина — time.

Существует и time with time zone (timetz), но для большинства новых схем он не нужен.

UUID

uuid хранит 128-битный идентификатор. Его обычная текстовая запись выглядит так:

text
a57eec41-1923-4ad8-9ed6-728ed83194af

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

Для идентификатора строки по-прежнему нужны PRIMARY KEY или UNIQUE. UUID также не заменяет проверку прав доступа: по форме идентификатора нельзя судить, защищены ли данные.

JSON и JSONB

Эти типы хранят JSON-данные: объекты, массивы и отдельные значения. Например:

json
{"color": "blue", "size": 42, "available": true}

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

json полезен, если важно сохранить исходное представление документа. Порядок элементов массива сохраняется в обоих типах.

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

Массивы

Массив хранит несколько элементов одного типа в одном значении. Для объявления к названию типа добавляют []:

Поддерживаются и многомерные массивы. По умолчанию индексация начинается с 1.

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

Запись integer[3] сама по себе не ограничивает длину массива тремя элементами. Для такого правила нужно дополнительное ограничение CHECK.

Другие полезные типы

Есть также тип money, но для универсального хранения денежных сумм обычно удобнее numeric: формат money зависит от региональных настроек.

Отдельного упоминания заслуживают:

- Перечисления (ENUM) — пользовательские типы с заранее заданным набором значений, например статусами заказа.
- Диапазоны — типы вроде daterange, int4range и tstzrange, которые хранят диапазон с границами. Мультидиапазоны объединяют несколько таких диапазонов.
- HSTORE — тип из расширения hstore для пар «ключ — значение». Ключи и значения текстовые; значение также может быть NULL. Для вложенных структур обычно подходит jsonb.

Пример таблицы

sql
CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name text NOT NULL,
    price numeric(10, 2) NOT NULL CHECK (price >= 0),
    quantity integer NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    is_available boolean NOT NULL DEFAULT TRUE,
    created_at timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
    properties jsonb NOT NULL DEFAULT '{}'::jsonb
);

Тип задаёт допустимый формат значения. Ограничения дополняют его: NOT NULL запрещает отсутствие значения, CHECK проверяет условие, а PRIMARY KEY обеспечивает уникальность идентификатора и запрещает NULL.

Функция CAST — изменить тип данных

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

SELECT CAST(column AS VARCHAR)
FROM table

Также изменить тип данных можно с помощью специального синтаксиса с двумя двоеточиями («::»):

SELECT column::VARCHAR
FROM table

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

SELECT '2022-12-31'::DATE

Результат:
31/12/22