serial или identity в PostgreSQL

— Макс, привет! — стажер Коля появился у стола Макса. — Минутка есть?

Макс оторвался от монитора, и с улыбкой кивнул:

— Привет, Коля. Для тебя — всегда. Что на этот раз? Опять VACUUM что-то не убрал?

— Нет, с этим я, кажется, разобрался, спасибо! — с гордостью ответил стажер. — У меня вопрос по созданию таблиц. Я тут делаю новую табличку. По привычке для первичного ключа написал id serial PRIMARY KEY. А наш тимлид посмотрел и сказал переделать на GENERATED AS IDENTITY. Говорит, serial — это «легаси». Я почитал, но не понял, в чем соль? И то, и другое просто автоинкремент, разве нет?

Макс хитро прищурился.

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

Он открыл консоль.

— Смотри. Что такое serial по своей сути? Это просто синтаксический сахар. Когда ты пишешь id serial, PostgreSQL за кулисами создает для тебя последовательность (sequence) и ставит ее как значение DEFAULT для твоего столбца id. Удобно, быстро, но есть подвох.

Макс создал тестовую таблицу:

CREATE TABLE legacy_invoices (
    id serial PRIMARY KEY,
    amount numeric
);

— Теперь смотри, — он вставил одну строку как положено.

INSERT INTO legacy_invoices (amount) VALUES (100); -- id стал 1

— А теперь представим, что к нам пришел разработчик, который решил вставить запись с ID вручную. Сейчас не важно зачем.

INSERT INTO legacy_invoices (id, amount) VALUES (2, 200);

— Видишь? Запись вставилась без проблем. А теперь главный фокус. Что будет, если мы снова вставим запись обычным способом?

INSERT INTO legacy_invoices (amount) VALUES (300);
-- ERROR:  duplicate key value violates unique constraint "legacy_invoices_pkey"
-- DETAIL: Key (id)=(2) already exists.

Глаза Коли округлились.

— Ой! Как так?

— А вот так. Наш sequence ничего не знает о том, что мы вручную вставили значение 2. Его счетчик остался на 1, и при следующей вставке он честно выдал следующее значение — 2. А так как запись с id=2 уже есть, мы получили конфликт. Это одна из классических проблем, которая может «всплыть» ночью и остановить важный процесс.

— А теперь современный, стандартный подход, — продолжил Макс, создавая новую таблицу.

CREATE TABLE modern_invoices (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount numeric
);

— Ключевые слова здесь GENERATED ALWAYS AS IDENTITY. Это уже не просто DEFAULT, а жесткое свойство столбца, прописанное в стандарте SQL. Оно говорит базе: «Значения для этого столбца всегда генерируются системой. Руками не трогать!». Попробуем повторить наш трюк.

INSERT INTO modern_invoices (id, amount) VALUES (2, 200);
-- ERROR: cannot insert a value into an identity column
-- DETAIL: Column "id" is an identity column defined as GENERATED ALWAYS.
-- HINT: Use OVERRIDING SYSTEM VALUE to override.

— Вот! — Макс торжествующе указал на экран. — PostgreSQL просто не дал нам совершить ошибку. Он защищает нас от случайных или необдуманных вставок. Если тебе действительно нужно вставить свое значение, ты должен явно указать OVERRIDING SYSTEM VALUE. Это заставляет подтвердить, что ты понимаешь, что делаешь.

— А что за режим BY DEFAULT? — тут же спросил Коля.

— Хороший вопрос! GENERATED BY DEFAULT AS IDENTITY — это мягкий режим. Он позволит тебе вставить свое значение без OVERRIDING SYSTEM VALUE, как serial. Но для большинства обычных таблиц, особенно с первичными ключами, GENERATED ALWAYS — самый безопасный и предсказуемый выбор.

Макс немного помолчал и добавил:

— Только не считай IDENTITY волшебной синхронизацией. Если осознанно применить OVERRIDING SYSTEM VALUE, связанная последовательность тоже не узнает о ручном значении. После импорта идентификаторов всё равно нужно сверить sequence и при необходимости вызвать setval. Преимущество ALWAYS в том, что опасное действие становится явным, а связь столбца с генератором описывается как стандартное свойство схемы.

Коля задумчиво смотрел на экран.

— Теперь понятно. serial — это удобная обёртка над DEFAULT nextval(...), а GENERATED AS IDENTITY — явное свойство столбца и дополнительная защита от случайной ручной вставки.

— Именно! — подытожил Макс. — В нашем деле, где каждая транзакция на счету, такие «мелочи» критически важны. Так что тимлид твой абсолютно прав. Иди, переделывай. И запомни: дьявол, как и будущие баги, кроется в деталях.