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

В прошлом уроке мы вставляли книги с номерами 1, 2, 3, написанными руками. Пока магазином пользуешься один, это работает. Но когда заказы оформляют сто покупателей одновременно, каждому нужен свой номер, и раздавать их должен кто-то один. В PostgreSQL этим занимается база.

Аппарат с талончиками

Вспомни аппарат электронной очереди в банке. Нажимаешь кнопку, получаешь талон 47. Следующий человек получает 48. Аппарат не спрашивает, свободен ли номер, он помнит последний выданный и прибавляет единицу. Двое людей не получат один номер, даже если нажмут кнопку одновременно.

А если ты взял талон 49 и ушёл, не дождавшись очереди? Номер 49 никто больше не получит. Аппарат назад не крутит. Запомни этот момент, он ещё пригодится.

В PostgreSQL такой аппарат называется последовательностью. Это отдельный объект в базе, и создать его можно руками:

CREATE SEQUENCE ticket_seq;

SELECT nextval('ticket_seq');
SELECT nextval('ticket_seq');
SELECT currval('ticket_seq');

 nextval
---------
       1

 nextval
---------
       2

 currval
---------
       2

nextval нажимает кнопку и выдаёт следующий номер. currval показывает, какой номер последним получила твоя сессия, ничего не выдавая.

Руками последовательности почти никогда не трогают. Их создают за тебя два механизма: старый serial и новый identity.

serial: старый способ

CREATE TABLE authors (
  id   serial PRIMARY KEY,
  name text NOT NULL
);

serial выглядит как тип, но это сокращение. Postgres разворачивает его в три вещи: колонку integer, последовательность authors_id_seq и значение по умолчанию nextval('authors_id_seq'). Если открыть описание таблицы командой \d authors в psql, это видно:

 Column |  Type   | Collation | Nullable |               Default
--------+---------+-----------+----------+-------------------------------------
 id     | integer |           | not null | nextval('authors_id_seq'::regclass)
 name   | text    |           | not null |

Значение по умолчанию подставляется, только когда ты не указал id сам. А указать его никто не запрещает. Отсюда классическая авария:

INSERT INTO authors (name) VALUES ('Адитья Бхаргава'), ('Максим Ильяхов');
INSERT INTO authors (id, name) VALUES (3, 'Стив Макконнелл');
INSERT INTO authors (name) VALUES ('Роберт Мартин');

ERROR:  duplicate key value violates unique constraint "authors_pkey"
DETAIL:  Key (id)=(3) already exists.

Первые два автора получили 1 и 2 из последовательности. Третьего вставили с номером 3 руками, в обход аппарата. Аппарат об этом не знает и четвёртому автору честно выдаёт 3, которое уже занято. На практике так ломаются базы после ручного импорта данных: все вставки падают, пока последовательность не подвинут.