Числа в PostgreSQL: bigint, numeric, boolean и как не потерять деньги

← назад к разделу

PostgreSQL предлагает много числовых типов, и выбор не очевидный. На практике почти все ошибки сводятся к трём сценариям: переполнился счётчик id, потерялись копейки из-за округления, сломался флаг потому что хранился как число. Разберём каждый по очереди.

Проблема с id: почему integer однажды закончится

Представьте: вы создаёте таблицу заказов и выбираете integer для id — кажется, 2 миллиарда строк хватит на сто лет. Через несколько лет таблица разрастается, id исчерпывается, и узнаёте об этом из аварии в пятницу вечером.

Счётчик при этом не «перескакивает» на отрицательные числа — база просто отказывается писать:

-- ошибка: integer out of range
SELECT 2147483647::integer + 1;

Миграция integer → bigint на живой большой таблице — это дни работы:

• ALTER TABLE … ALTER COLUMN … TYPE bigint требует эксклюзивной блокировки и переписывает всю таблицу;
• обходные пути сложны: новая колонка, копирование данных, переключение.

Поэтому правило простое: id таблицы — всегда bigint.

CREATE TABLE order_item (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    order_id    bigint    NOT NULL,
    quantity    integer   NOT NULL CHECK (quantity > 0),
    weight_g    integer   NOT NULL
);

Три целочисленных типа и их диапазоны:

Тип	Размер	Диапазон
smallint	2 байта	−32 768 … 32 767
integer	4 байта	±2.1 миллиарда
bigint	8 байт	±9.2 квинтиллиона

Насколько это «больше», удобно посмотреть запросом:

живой пример

SELECT 2147483647::integer          AS integer_max,
       9223372036854775807::bigint  AS bigint_max,
       9223372036854775807 / 2147483647 AS bigint_is_times_bigger;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Последняя колонка и есть ответ: потолок bigint в 4 294 967 298 раз выше, чем у integer. Разница в размере строки — 4 байта. Стоимость этих 4 байт несравнима со стоимостью инцидента.

Когда smallint всё же уместен

smallint имеет смысл только для фиксированных шкал, где переполнение физически невозможно:

day_of_week     smallint NOT NULL CHECK (day_of_week BETWEEN 1 AND 7),
timezone_offset smallint NOT NULL  -- смещение в минутах от UTC

Для счётчиков, лимитов, остатков, любых «бизнесовых» чисел — только integer или bigint. smallint экономит 2 байта, да и то не всегда: поля в строке выравниваются по границам, и рядом с integer сэкономленное уходит в отступ. Проверить просто — строка из smallint и integer занимает ровно столько же, сколько строка из двух integer. Риск переполнения такая экономия точно не оправдывает.

GENERATED ALWAYS AS IDENTITY вместо serial

Раньше для автоинкрементного id писали serial или bigserial:

-- старый способ — не рекомендуется
CREATE TABLE foo (
    id bigserial PRIMARY KEY
);

serial — это не настоящий тип данных, а сокращение, которое PostgreSQL разворачивает в integer с последовательностью и значением по умолчанию. В примере выше стоит bigserial — он разворачивается в bigint, и путать их не стоит: serial даёт счётчик всего на четыре байта, с тем самым потолком в два миллиарда. Проблемы у обоих одинаковые:

• последовательность — отдельный объект, и права на неё выдаются отдельно: дали пользователю право писать в таблицу, а вставка всё равно падает, потому что на последовательность прав нет;
• нет способа запретить явную вставку произвольного id — приложение может подсунуть свой, и потом последовательность начнёт выдавать уже занятые номера;
• есть угловые случаи в pg_dump, из-за которых связь колонки с последовательностью переносится не так, как ожидаешь.

Начиная с PostgreSQL 10 стандартный способ — GENERATED ALWAYS AS IDENTITY:

-- современный способ
CREATE TABLE foo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

ALWAYS означает, что PostgreSQL сам генерирует id, а обычный INSERT со своим значением отвергнет. Это защита от случайных конфликтов.

Запрет не абсолютный, и это важно. При переносе данных старые id нужно сохранить как есть — для этого существует форма INSERT … OVERRIDING SYSTEM VALUE, и база сама подсказывает её в тексте ошибки. Переделывать ради разовой заливки колонку не нужно.

Мягкая версия BY DEFAULT AS IDENTITY — для другого случая: когда приложение штатно и постоянно проставляет id само. Она разрешает явный insert без оговорок, а если значения нет — генерирует автоматически.

Когда GENERATED ALWAYS AS IDENTITY не подходит: если вам нужен глобально уникальный id без обращения к базе — например, для распределённых сервисов. В таком случае используют UUID v7.

Следствие, которое обнаруживают на первой же неделе: номера из последовательности не возвращаются. Транзакция откатилась, вставка нарушила уникальность, приложение отменило операцию — номер уже выдан и потрачен. В id будут дыры, и это не поломка: последовательность работает вне транзакций нарочно, иначе выдача номеров превратилась бы в очередь и вставки встали бы друг за другом. Отсюда два практических правила. count(*) никогда не совпадает с max(id), и строить на этом проверки нельзя. И номер из последовательности не годится на роль номера документа для человека: в счетах и накладных пропуски недопустимы, такие номера выдают отдельной таблицей-счётчиком под блокировкой, платя за это очередью.

Деньги и числа с плавающей точкой: классическая ловушка

Самая распространённая ошибка с числами в PostgreSQL — хранить деньги в float или double precision. Посмотрим, почему это опасно.

Компьютер хранит float в двоичном виде. Проблема в том, что большинство десятичных дробей нельзя точно представить в двоичном. Например, 0.1 в двоичном — бесконечная дробь, и до конца её не записать:

живой пример

SELECT 0.1::double precision + 0.2::double precision AS float_sum,
       0.1::numeric(10,2) + 0.2::numeric(10,2)       AS numeric_sum,
       0.1::double precision + 0.2::double precision = 0.3::double precision AS float_equals_03;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Запрос вернёт 0.30000000000000004, 0.30 и f: в double precision сумма двух дробей не равна 0.3, а в numeric — равна.

Для отдельной операции погрешность крошечная: double precision держит около шестнадцати значащих цифр, и ошибка каждого сложения прячется где-то там. Но она копится. Сложите в базе десять миллионов раз по копейке, и double precision выдаст 99999.99998630969 вместо ровных 100000.00 — расхождение уже в пятом знаке после запятой и растёт вместе с оборотом. При сверке с банком такие суммы не сойдутся, а расчёт по закону обязан сходиться точно.

Правильный тип для денег — numeric(p, s), где p — общее количество значимых цифр, s — знаков после запятой. numeric хранит число точно, как десятичные цифры:

amount_total      numeric(15, 2) NOT NULL,   -- до 13 цифр до запятой, 2 после
exchange_rate     numeric(20, 8) NOT NULL,   -- курсы: 8 знаков после запятой
discount_percent  numeric(5, 2)  NOT NULL CHECK (discount_percent BETWEEN 0 AND 100)

Альтернатива: копейки в bigint

Ещё один рабочий подход — хранить денежные суммы в целых единицах наименьшего номинала:

amount_cents bigint NOT NULL CHECK (amount_cents >= 0)

Плюсы: быстрее numeric, целочисленные операции не дают погрешности. Минусы: неудобно для систем с разной точностью (криптовалюта — 8 знаков, рубли — 2, некоторые национальные валюты — 3). Один пропущенный делитель в коде превращается в ошибку расчёта.

Для обычного маркетплейса или сервиса подписок — numeric(p, s).

Что происходит на стороне Java

numeric приезжает в Java как BigDecimal, и это единственное правильное отображение. Объявить в записи double для колонки numeric(15,2) технически можно — драйвер преобразует, — но тогда весь смысл точного типа теряется на границе: значение проходит через двоичную дробь, и 0.1 снова перестаёт быть 0.1.

Дальше две ловушки самого BigDecimal. Первая: equals сравнивает ещё и шкалу, поэтому new BigDecimal("0.10").equals(new BigDecimal("0.1")) — это false, хотя числа равны. Сравнивать значения надо через compareTo(...) == 0, а в тестах — assertThat(actual).isEqualByComparingTo("0.10"). Из базы одна и та же сумма может приехать с разной шкалой (numeric без точности хранит столько знаков, сколько дали), и тест на equals начинает падать в самый неподходящий момент.

Вторая: деление без указания шкалы. amount.divide(new BigDecimal("3")) бросит ArithmeticException: Non-terminating decimal expansion; no exact representable decimal result — точного результата нет, а угадывать округление BigDecimal не станет. Правильная форма всегда с двумя аргументами: amount.divide(new BigDecimal("3"), 2, RoundingMode.HALF_UP).

Округление и границы при вставке

У numeric(15,2) две границы ведут себя по-разному, и это стоит знать заранее. Лишние знаки после запятой молча округляются: записали 199.006 — в таблице лежит 199.01, никакой ошибки. А переполнение целой части честно падает: numeric field overflow. Поэтому расхождение в копейках при сверке обычно ищут не в базе, а в том месте кода, которое отдало значение с лишней точностью.

Две оговорки про сам тип. numeric можно объявить и без точности — тогда он хранит столько знаков, сколько дали, и ничего не округляет; удобно для промежуточных расчётов и неудобно для денег, где шкала должна быть частью контракта. И у numeric есть значение NaN (у целых типов его нет): оно попадает в колонку при вычислениях вроде 'NaN'::numeric, считается больше любого числа при сортировке и превращает в NaN любую сумму, в которую попало, — то есть один такой мусорный ряд ломает весь отчёт разом.

Когда numeric не брать

numeric — программная арифметика: сложение двух значений стоит заметно дороже, чем сложение двух целых, которое процессор делает одной командой. На тысячах строк это незаметно, а на агрегации по десяткам миллионов разница видна в плане запроса. Отсюда приём, который живёт в аналитике: хранить деньги в bigint в копейках и делить на сто только на выводе. Для обычной OLTP-базы, где считают по сотням строк, он не нужен — там numeric честнее, потому что шкала записана в схеме.

round и avg: что ломается в первых же запросах

avg(total_amount) по колонке numeric возвращает numeric с длинным хвостом — и округлять его надо последним шагом, на выводе: round(avg(total_amount), 2). А вот та же попытка с колонкой double precision падает: function round(double precision, integer) does not exist. Функции округления до знака у двоичного типа просто нет, и это — самый частый отказ при решении задач на отчёты. Ошибка сообщает не о том, что вы забыли функцию, а о том, что колонка объявлена не тем типом; лечится либо правильным типом в схеме, либо приведением round(avg(x)::numeric, 2) — второе честно работает, но не отменяет первого.

Тип money: почему его не используют

В PostgreSQL есть встроенный тип money. Он выглядит удобно, но на практике не используется:

• формат задаёт параметр lc_monetary — серверный, но его можно переопределить и на одну сессию, так что одна и та же сумма на разных соединениях выглядит по-разному;
• не хранит код валюты — нельзя различить рубли и доллары;
• конвертация работает только в одну сторону спокойно: money::numeric отдаёт число как есть, а вот строку в money база разбирает по тому же lc_monetary — на сервере с другой настройкой та же строка прочитается иначе или не прочитается вовсе.

Для любой задачи с деньгами — numeric(p, s) лучше.

Когда float всё же уместен

real и double precision не запрещены — они нужны там, где небольшая погрешность допустима по природе данных. У double precision пятнадцать-шестнадцать значащих цифр: для задержки в миллисекундах это запас в десять порядков, а для суммы в копейках запаса нет, и первая же сумма в миллион рублей теряет копейку:

• метрики мониторинга: загрузка процессора, задержка p95;
• научные расчёты: вес, температура, расстояние (входные данные уже приближённые);
• машинное обучение: эмбеддинги, числовые признаки.

Везде, где числа должны точно сходиться — деньги, баллы лояльности, учётные количества — float не подходит.

Boolean — это boolean, не число

Ещё одна частая ошибка — хранить булев флаг как число или строку:

-- частая ошибка
is_active   smallint NOT NULL DEFAULT 1,
is_active   varchar(1) NOT NULL DEFAULT 'Y' CHECK (is_active IN ('Y','N')),
is_active   char(1) NOT NULL DEFAULT 'Y'

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

живой пример

SELECT 2::smallint = 1 AS flag_as_number,
       'Y' = 'y'       AS flag_as_text,
       'yes'::boolean  AS flag_as_boolean;

Запустить

Запуск примеров доступен в платном доступе. Там этот же код выполняется прямо в статье: редактор, запуск и проверка рядом с абзацем. Три дня бесплатно →

Первые две колонки — f: двойка вместо единицы и строчная буква вместо заглавной молча выключают флаг, ошибки при этом нет. Третья — t: boolean понимает yes, on, 1, а ничего постороннего в него не запишешь.

PostgreSQL имеет встроенный тип boolean — используйте его:

is_active   boolean NOT NULL DEFAULT true,
is_deleted  boolean NOT NULL DEFAULT false

boolean занимает один байт. Он говорит то, что значит: is_active это да или нет, а не «0 или 1, про которые надо помнить». И с ним пишут WHERE is_active AND NOT is_deleted, без = 1 и приведений.

Глубже: генерируемые колонки и доменырасширенное

Значение, которое всегда выводится из других колонок, хочется не хранить, чтобы оно не разошлось: нормализованный email, вектор для поиска, сумма с налогом. Триггер для этого тяжеловесен, а вычисление в каждом запросе дублирует логику. Между ними есть штатное средство:

ALTER TABLE customer
    ADD COLUMN email_norm text GENERATED ALWAYS AS (lower(trim(email))) STORED;

Генерируемая колонка пересчитывается базой при каждой вставке и обновлении строки, писать в неё нельзя, читать и индексировать можно как обычную. Требование одно: выражение должно быть неизменяемым (IMMUTABLE), то есть давать один результат для одних аргументов всегда. lower() и арифметика годятся; now(), random(), обращение к другим таблицам и to_tsvector без явно указанной конфигурации нет: to_tsvector('russian', title) с явной конфигурацией неизменяем, без неё зависит от настройки сессии, и на этом ломается DDL в статье про веса поиска. Колонка не может ссылаться на другую генерируемую и не подходит для итогов по другим таблицам: сумма позиций заказа считается запросом или триггером, а не генерируемой колонкой в orders.

Домен это свой тип с ограничением, чтобы правило не повторять в каждой таблице:

CREATE DOMAIN money_rub AS numeric(14, 2) CHECK (VALUE >= 0);
CREATE DOMAIN email AS text CHECK (VALUE ~ '^[^@]+@[^@]+$');

Дальше колонки объявляют как price money_rub, и CHECK приезжает с типом. Составной тип (CREATE TYPE address AS (city text, street text)) описывает структуру, но как колонка используется редко: у его полей нет своих ограничений и индексов, и отдельная таблица или JSONB обычно удобнее.

Коротко

• Id таблицы — всегда bigint: 4 лишних байта дешевле миграции int → bigint на большой таблице, а при переполнении integer вставка просто падает с integer out of range.
• Автоинкремент — GENERATED ALWAYS AS IDENTITY вместо устаревших serial/bigserial.
• smallint — только для фиксированных шкал (день недели, смещение UTC). Для счётчиков — integer/bigint.
• Деньги — numeric(p, s): float накапливает погрешность, money привязан к локали и не хранит код валюты. Альтернатива — копейки в bigint, но она ограничена при разной точности валют.
• real/double precision — допустимы для метрик, научных данных, машинного обучения. Не для финансов.
• Флаги — boolean, не smallint, не varchar('Y'/'N').
• Выводимое значение держат генерируемой колонкой GENERATED ALWAYS AS (…) STORED с неизменяемым выражением; повторяющееся правило типа выносят в DOMAIN с CHECK.
• Номера последовательности не возвращаются при откате: дыры в id — норма, count(*) не равен max(id), а номера документов для людей так не выдают.
• numeric в Java — только BigDecimal: сравнивать compareTo, делить с указанием шкалы и RoundingMode; лишние знаки при вставке молча округляются, а переполнение падает.
• round(double precision, integer) в PostgreSQL не существует: отказ в отчёте означает, что колонка объявлена двоичным типом, а не десятичным.

Что почитать дальше

• Строковые типы — text против varchar, когда что выбирать.
• Время и таймзоны — timestamptz и почему timezone имеет значение.
• UUID и идентификаторы — когда нужен UUID вместо bigint.
• Enum и перечисления — как хранить статусы и категории.