Типы данных в PostgreSQL: частые ошибки новичков

В PostgreSQL 16 ошибка в типе колонки редко проявляется сразу: сначала дата теряет часовой пояс, деньги начинают округляться, а строковый идентификатор приходится приводить к числу в каждом запросе. Ниже — короткая практическая инструкция, после которой вы сможете выбрать типы для простой таблицы заказов и проверить их поведение примерно за 20–30 минут.

Что подготовить до начала

Нужен PostgreSQL 16 или совместимый сервер, база данных, роль с правом создавать таблицы и любой клиент: psql, DBeaver или встроенный редактор в вашей IDE. Команды ниже используют только стандартный SQL PostgreSQL и не требуют расширений.

Проверьте версию перед экспериментом:

SELECT version();

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

Тип данных — это не подпись к колонке. Он определяет, какие значения база примет, как их сравнит, сколько места выделит и какие ошибки заметит за вас.

Шаг 1. Отделите идентификатор от значения

Для ключей таблиц обычно подходит bigint GENERATED ALWAYS AS IDENTITY. Такой столбец хранит целое число, а база сама выдаёт новые значения. Не используйте для идентификатора numeric, text или вещественный тип без причины: они усложняют индексы, сортировку и связи между таблицами.

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

Шаг 2. Выберите типы для заказа

Создайте тестовую таблицу целиком. Скрипт можно запускать в базе, где нет таблицы orders_type_demo: команда DROP TABLE удалит только её.

DROP TABLE IF EXISTS orders_type_demo;

CREATE TABLE orders_type_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_code text NOT NULL,
    customer_email text NOT NULL,
    amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
    quantity integer NOT NULL CHECK (quantity > 0),
    paid_at timestamptz,
    status text NOT NULL CHECK (status IN ('new', 'paid', 'cancelled')),
    attributes jsonb NOT NULL DEFAULT '{}'::jsonb,
    tags text[] NOT NULL DEFAULT '{}'
);

INSERT INTO orders_type_demo
    (external_code, customer_email, amount, quantity, paid_at, status, attributes, tags)
VALUES
    ('ORD-2025-0001', 'anna@example.com', 1499.90, 2,
     '2025-03-08 10:15:00+03', 'paid',
     '{"source":"site","delivery":"courier"}'::jsonb,
     ARRAY['new-customer', 'priority']),
    ('ORD-2025-0002', 'oleg@example.com', 0.00, 1,
     NULL, 'new',
     '{}'::jsonb, ARRAY[]::text[]);

SELECT id, external_code, amount, quantity, paid_at, status
FROM orders_type_demo
ORDER BY id;

Проверка: таблица должна создаться, вставка — добавить две строки, а id — заполниться автоматически. Если сервер ругается на синтаксис GENERATED ALWAYS AS IDENTITY, проверьте версию: этот синтаксис рассчитан на современные версии PostgreSQL, а для старого сервера нужна отдельная проверка документации.

Шаг 3. Не храните деньги во float

real и double precision — двоичные числа с плавающей точкой. Они удобны для научных вычислений, но не обещают точное десятичное представление суммы. Для цены, скидки и баланса используйте numeric с явно заданной точностью, например numeric(12, 2).

SELECT
    0.1::double precision + 0.2::double precision AS float_result,
    0.1::numeric + 0.2::numeric AS exact_result,
    19.999::numeric(12, 2) AS rounded_value;

Проверка: результат для numeric будет равен 0.3, а значение с тремя знаками после запятой будет приведено к масштабу колонки. Это не отменяет проверку бизнес-правил: количество знаков должно соответствовать вашей валюте и расчётам.

Шаг 4. Разберите даты и часовые пояса

Главная ошибка новичка — выбирать timestamp для момента, который должен быть одинаковым для пользователей в разных часовых поясах. timestamp without time zone хранит только дату и время без информации о зоне. Для времени оплаты, события или отправки письма чаще нужен timestamptz.

SELECT
    TIMESTAMP '2025-03-08 10:15:00' AS local_time_without_zone,
    TIMESTAMPTZ '2025-03-08 10:15:00+03' AS instant_with_zone,
    CURRENT_TIMESTAMP AS database_current_time;

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

Шаг 5. Не превращайте каждую строку в varchar

text и varchar без ограничения обычно не дают полезной защиты. Если длина действительно ограничена бизнес-правилом, задайте её через ограничение CHECK или подходящий домен. Для почты, внешнего кода и названия товара не выбирайте тип только потому, что он кажется привычнее.

ALTER TABLE orders_type_demo
ADD CONSTRAINT customer_email_length_check
CHECK (char_length(customer_email) BETWEEN 3 AND 320);

INSERT INTO orders_type_demo
    (external_code, customer_email, amount, quantity, status)
VALUES
    ('ORD-2025-0003', 'x', 100.00, 1, 'new');

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

Шаг 6. Используйте JSONB только для действительно гибких полей

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

SELECT
    external_code,
    attributes ->> 'source' AS order_source
FROM orders_type_demo
WHERE attributes @> '{"delivery":"courier"}'::jsonb;

Проверка: запрос должен вернуть заказ с кодом ORD-2025-0001. Для часто используемого пути в JSONB понадобится отдельный индекс; его размер и пользу проверяйте на реальном объёме данных, а не на двух тестовых строках.

Шаг 7. Проверьте типы через каталог базы

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

SELECT
    column_name,
    data_type,
    udt_name,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_name = 'orders_type_demo'
ORDER BY ordinal_position;

Проверка: в выдаче должны быть bigint, text, numeric, integer, timestamp with time zone, jsonb и массив текстовых значений. Если тип оказался не тем, который вы ожидали, исправляйте схему миграцией, а не преобразованием в каждом запросе.

Если не получилось

• invalid input syntax for type integer. В колонку целого типа передали текст, пустую строку или число с дробной частью. Проверьте входные данные и решите, действительно ли колонка должна быть целой.
• numeric field overflow. Значение не помещается в объявленную точность, например в numeric(12, 2). Увеличьте точность только после проверки максимальной суммы, а не автоматически.
• date/time field value out of range. Дата записана в неоднозначном формате или не существует. Передавайте ISO-значения с явным часовым поясом для моментов времени.
• violates check constraint. Значение нарушило правило CHECK: отрицательная сумма, нулевая quantity или неизвестный статус. Исправьте данные либо измените правило после согласования требований.
• operator does not exist. Сравниваются несовместимые типы, например text и integer. Не добавляйте бездумное приведение в запрос: сначала проверьте, почему схема хранит данные в разных типах.

Где типовой подход ломается

Универсальная таблица с колонками key, value и типом в соседнем поле кажется гибкой, но переносит проверку данных в приложение. В результате база не знает, что сумма должна быть неотрицательной, дата — корректной, а статус — одним из допустимых значений.

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

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

Что делать дальше

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