UPSERT: вставить-или-обновить

Одна команда, которая вставляет новую строку или обновляет существующую — без проверки «а есть ли уже?».

UPSERT (UPdate + inSERT) — атомарная операция «вставить строку, а если такая уже есть (конфликт по ключу) — обновить её».

Классическая ошибка новичка: «сначала SELECT — проверю, есть ли запись; если нет, INSERT, если есть, UPDATE». Между вашим SELECT и INSERT другой клиент успеет вставить ту же строку — получите дубль или ошибку уникальности. UPSERT делает всё одной атомарной командой, и гонки исчезают.

Зачем это на практике

UPSERT — основа идемпотентности. Идемпотентная операция даёт один и тот же результат, сколько раз её ни повтори. Это критично, когда сообщение из очереди может прийти дважды, пользователь дважды нажал «Сохранить», а импорт перезапустили. Типичные задачи: счётчики (просмотры, лайки), кэш «ключ → значение», синхронизация справочников, накопление статистики по дням.

INSERT ... ON CONFLICT DO UPDATE

Стандарт SQLite и PostgreSQL. Сначала объявляем, по какому ограничению ловить конфликт (обычно это PRIMARY KEY или UNIQUE-колонка), затем — что делать. В DO UPDATE доступна псевдотаблица excluded — это та строка, которую мы пытались вставить. Запустите пример со счётчиком просмотров статей.

CREATE TABLE page_views (
  slug TEXT PRIMARY KEY,
  views INTEGER NOT NULL DEFAULT 0
);

INSERT INTO page_views (slug, views) VALUES ('sql-upsert', 1)
ON CONFLICT(slug) DO UPDATE SET views = views + 1;

INSERT INTO page_views (slug, views) VALUES ('sql-upsert', 1)
ON CONFLICT(slug) DO UPDATE SET views = views + 1;

INSERT INTO page_views (slug, views) VALUES ('sql-cte', 1)
ON CONFLICT(slug) DO UPDATE SET views = views + 1;

SELECT slug, views FROM page_views ORDER BY slug;

Вывод:

sql-cte|1
sql-upsert|2

Первая вставка sql-upsert создала строку с views = 1. Вторая попала в конфликт по slug и вместо ошибки прибавила единицу к существующему значению. Никаких SELECT и проверок «существует ли» — одна команда на любой случай.

Псевдотаблица excluded

Когда вставляете сразу несколько колонок, удобно брать новые значения из excluded, а не дублировать литералы. Так UPSERT-кэш «ключ → значение» обновляет значение и время на свежие.

CREATE TABLE cache (
  k TEXT PRIMARY KEY,
  v TEXT NOT NULL,
  updated_at INTEGER NOT NULL
);

INSERT INTO cache (k, v, updated_at) VALUES ('user:1', 'Anna', 100)
ON CONFLICT(k) DO UPDATE SET v = excluded.v, updated_at = excluded.updated_at;

INSERT INTO cache (k, v, updated_at) VALUES ('user:1', 'Anna Smith', 200)
ON CONFLICT(k) DO UPDATE SET v = excluded.v, updated_at = excluded.updated_at;

SELECT k, v, updated_at FROM cache;

Вывод:

user:1|Anna Smith|200

DO NOTHING: вставить, только если новой нет

Иногда обновлять не нужно — достаточно «вставь, а если уже есть, тихо пропусти». Для этого ON CONFLICT DO NOTHING. Удобно для справочников и тегов: повторный импорт не падает с ошибкой уникальности.

CREATE TABLE tags (name TEXT PRIMARY KEY);
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT(name) DO NOTHING;
INSERT INTO tags (name) VALUES ('sql') ON CONFLICT(name) DO NOTHING;
INSERT INTO tags (name) VALUES ('db')  ON CONFLICT(name) DO NOTHING;
SELECT name FROM tags ORDER BY name;

Вывод:

db
sql

Оператор MERGE

В стандарте SQL и «больших» СУБД (PostgreSQL 15+, SQL Server, Oracle) есть более общий оператор MERGE. Он сопоставляет целевую таблицу с источником по условию и описывает отдельно ветки «совпало» и «не совпало». MERGE мощнее (умеет ещё и удалять), но многословнее, поэтому для простого «вставить-или-обновить» обычно берут ON CONFLICT. Синтаксис только для чтения — в нашей SQLite-песочнице он не выполнится:

MERGE INTO page_views AS t
USING (SELECT 'sql-upsert' AS slug, 1 AS inc) AS s
ON t.slug = s.slug
WHEN MATCHED THEN
  UPDATE SET views = t.views + s.inc
WHEN NOT MATCHED THEN
  INSERT (slug, views) VALUES (s.slug, s.inc);

Как это работает под капотом

UPSERT опирается на ограничение уникальности. Движок пытается вставить строку; если нарушается UNIQUE/PRIMARY KEY, вместо отката он переключается на ветку DO UPDATE — и всё это внутри одной атомарной операции под защитой блокировки строки. Поэтому два параллельных UPSERT не создадут дубль: один вставит, второй увидит конфликт и обновит. Ключевой нюанс: ON CONFLICT работает только при наличии подходящего ограничения. Нет UNIQUE на колонке — конфликту неоткуда взяться, и поведение будет не тем, что вы ждёте.

Частые ошибки

• Нет UNIQUE/PRIMARY KEY на колонке конфликта. Тогда ON CONFLICT(col) ни на что не среагирует — конфликт по ней просто не возникает.
• Путают excluded и текущую строку. excluded.v — то, что вы пытались вставить; просто v — то, что уже лежит в таблице. Для счётчика нужно views = views + 1, а не views = excluded.views.
• Ручной паттерн SELECT-затем-INSERT. Между ними есть окно гонки. Под нагрузкой это даёт дубли или ошибки уникальности — ровно то, что UPSERT и устраняет.
• Переносят синтаксис между СУБД дословно. MySQL пишет INSERT ... ON DUPLICATE KEY UPDATE и использует VALUES(col) вместо excluded. Синтаксис диалектно-зависим.

Итоги

• UPSERT = «вставить или обновить» одной атомарной командой, без проверок «а есть ли уже».
• INSERT ... ON CONFLICT(col) DO UPDATE SET ... — стандарт SQLite/PostgreSQL; excluded хранит несостоявшуюся вставку.
• DO NOTHING — тихо пропустить дубль (справочники, теги, повторный импорт).
• MERGE — более общий и многословный оператор для сложных слияний с ветками MATCHED/NOT MATCHED.
• Без UNIQUE/PRIMARY KEY на колонке конфликта UPSERT не сработает.