UPSERT через ON CONFLICT

В прошлом уроке INSERT падал с ошибкой, если строка с таким ключом уже была. Часто это правильно: два покупателя с одним номером означают ошибку. Но бывают задачи, где повтор ожидаем, и нужно не упасть, а обновить то, что уже есть. Сегодня разбираем, как PostgreSQL делает «вставь или обнови» одной командой. Примеры работают на учебной базе магазина.

Записная книжка

Ты записываешь телефон знакомого. Открываешь книжку на нужную букву: если его там нет, дописываешь новую строку, если уже есть, зачёркиваешь старый номер и пишешь новый. Две страницы с одним человеком тебе не нужны.

В базах эту операцию называют UPSERT: склейка английских update и insert. Отдельной команды с таким именем в PostgreSQL нет, вместо неё к обычному INSERT дописывают ON CONFLICT.

Склад магазина

Заведём таблицу остатков: сколько экземпляров каждой книги лежит на складе.

CREATE TABLE stock (
  book_id integer PRIMARY KEY REFERENCES books (id),
  qty     integer NOT NULL
);

INSERT INTO stock (book_id, qty) VALUES (1, 3), (3, 5), (7, 2);

Пришла поставка: ещё 4 экземпляра «Пикника на обочине». Обычный INSERT упирается в первичный ключ:

INSERT INTO stock (book_id, qty) VALUES (3, 4);

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

Почему не «проверь, потом вставь»

Первая идея: сначала SELECT, есть ли книга на складе, а потом в приложении решить, делать INSERT или UPDATE. Пока с базой работает один человек, так и будет. Но приходят две поставки одновременно, и два процесса склада выполняют одно и то же:

  процесс А                              процесс Б
  ─────────                              ─────────
  SELECT ... WHERE book_id = 4
  -- строки нет
                                         SELECT ... WHERE book_id = 4
                                         -- строки нет
  INSERT (4, 2)  -- успех
                                         INSERT (4, 3)
                                         -- ERROR: duplicate key

Оба увидели пустоту и оба решили вставлять. Такую ошибку называют гонкой: результат зависит от того, кто успел первым. Здесь поставка процесса Б потеряна. А если бы в таблице не было первичного ключа, появились бы две строки про одну книгу, и остаток стал бы неправдой.

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

ON CONFLICT DO NOTHING

Самый простой вариант: если строка уже есть, ничего не делать и не падать.

INSERT INTO stock (book_id, qty) VALUES (3, 4), (5, 6)
ON CONFLICT DO NOTHING;

INSERT 0 1

Из двух строк вставилась одна, книга 5. Строку про книгу 3 база молча пропустила, остаток остался прежним:

 book_id | qty
---------+-----
       1 |   3
       3 |   5
       5 |   6
       7 |   2
(4 rows)

DO NOTHING хорош, когда повтор означает «уже сделано». Например, приложение повторило запрос после сбоя сети, и второй раз записывать то же событие не нужно.