Автонумерация: serial и identity

Учимся автоматически нумеровать строки в PostgreSQL — двумя способами, старым и современным.

Автонумерация — это автоматическая выдача следующего уникального целого числа при вставке строки, чтобы не задавать id вручную.

Старый способ: SERIAL

Самый известный приём — псевдотип SERIAL. Это не настоящий тип, а сокращение: PostgreSQL создаёт целочисленный столбец, заводит для него отдельный счётчик (последовательность) и подставляет следующее значение при каждой вставке.

CREATE TABLE users (
    id    SERIAL PRIMARY KEY,
    email TEXT NOT NULL
);

-- id указывать не нужно — он подставится сам
INSERT INTO users (email) VALUES ('[email protected]');
INSERT INTO users (email) VALUES ('[email protected]');
-- теперь у Анны id = 1, у Бориса id = 2

Псевдотип	Базовый тип
`smallserial`	smallint
`serial`	integer
`bigserial`	bigint

Современный способ: GENERATED AS IDENTITY

Начиная с PostgreSQL 10 рекомендуют IDENTITY — это стандартный SQL и более чистое поведение, чем у SERIAL.

CREATE TABLE users (
    id    INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email TEXT NOT NULL
);

GENERATED ALWAYS AS IDENTITY — значение всегда генерирует база, вручную вставить нельзя (надёжнее).
GENERATED BY DEFAULT AS IDENTITY — база генерирует по умолчанию, но можно и задать вручную.

Почему IDENTITY лучше SERIAL: соответствует стандарту, права на столбец и счётчик связаны корректно, и нельзя случайно «забить» автоматический id своим значением.

Последовательности (sequence) под капотом

И SERIAL, и IDENTITY опираются на объект — последовательность (sequence). Это отдельный счётчик, который умеет выдавать следующее число.

-- Узнать текущее и следующее значение счётчика:
SELECT nextval('users_id_seq');   -- выдаёт и сдвигает счётчик
SELECT currval('users_id_seq');   -- последнее выданное в этой сессии

-- Создать свою последовательность:
CREATE SEQUENCE order_no START 1000 INCREMENT 1;

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

А в песочнице?

В переносимой SQLite-песочнице той же цели служит INTEGER PRIMARY KEY — он автоматически нумерует строки. Запустите пример:

CREATE TABLE notes (
    id   INTEGER PRIMARY KEY,
    text TEXT
);

INSERT INTO notes (text) VALUES ('первая');
INSERT INTO notes (text) VALUES ('вторая');

SELECT * FROM notes;

Вывод:

1|первая
2|вторая

Мы не передавали id — он назначился сам. В PostgreSQL то же поведение дают SERIAL и IDENTITY.

Итог

SERIAL/bigserial — классическая автонумерация через скрытую последовательность.
GENERATED AS IDENTITY — современный, стандартный и более безопасный способ; предпочитайте его.
Под обоими лежит sequence — отдельный счётчик; его рассинхрон даёт ошибки дубликата ключа.