re:re:actable
Урок 033 минуты чтения

Что такое UPSERT

На этой странице
Кратко

UPSERT — «вставить, а если строка с таким ключом уже есть — обновить (или пропустить)». В PostgreSQL это INSERT … ON CONFLICT (столбцы) DO UPDATE SET … | DO NOTHING; в MySQL — INSERT … ON DUPLICATE KEY UPDATE; в стандарте и PostgreSQL 15+ — MERGE. ON CONFLICT работает атомарно и безопасно при конкурентных запросах, в отличие от схемы «SELECT → если нет, INSERT, иначе UPDATE». Требуется уникальное ограничение или индекс по столбцам конфликта. Внутри DO UPDATE псевдотаблица EXCLUDED содержит значения, которые пытались вставить. Применения: счётчики, синхронизация справочников, идемпотентная обработка событий.

Вопрос

Что такое UPSERT и как его правильно реализовать?

ON CONFLICT DO UPDATE

INSERT INTO products (sku, title, price)
VALUES ($1, $2, $3)
ON CONFLICT (sku) DO UPDATE
SET title      = EXCLUDED.title,
    price      = EXCLUDED.price,
    updated_at = now()
RETURNING id, (xmax = 0) AS inserted;    -- приём: true, если строка вставлена, а не обновлена

ON CONFLICT DO NOTHING

-- идемпотентная обработка событий: повторная доставка ничего не сломает
INSERT INTO processed_events (event_id, processed_at)
VALUES ($1, now())
ON CONFLICT (event_id) DO NOTHING
RETURNING event_id;     -- 0 строк → событие уже обрабатывали

DO NOTHING ... RETURNING не вернёт существующую строку — если она нужна, делают отдельный SELECT или «пустой» DO UPDATE SET col = EXCLUDED.col.

Счётчики

INSERT INTO page_views (page_id, day, views)
VALUES ($1, current_date, 1)
ON CONFLICT (page_id, day) DO UPDATE
SET views = page_views.views + 1;

Условное обновление

-- обновлять только если пришли более свежие данные
INSERT INTO user_locations (user_id, lat, lng, reported_at)
VALUES ($1, $2, $3, $4)
ON CONFLICT (user_id) DO UPDATE
SET lat = EXCLUDED.lat, lng = EXCLUDED.lng, reported_at = EXCLUDED.reported_at
WHERE user_locations.reported_at < EXCLUDED.reported_at;

Почему не SELECT + INSERT

A: SELECT → нет строки        B: SELECT → нет строки
A: INSERT ✓                   B: INSERT ✗ unique_violation (или дубль, если UNIQUE нет)

MERGE (PostgreSQL 15+)

MERGE INTO inventory i
USING incoming_stock s ON s.sku = i.sku
WHEN MATCHED AND s.qty = 0 THEN DELETE
WHEN MATCHED THEN UPDATE SET qty = s.qty
WHEN NOT MATCHED THEN INSERT (sku, qty) VALUES (s.sku, s.qty);

MERGE удобен для синхронизации из таблицы-источника со сложными ветками, но при конкурентных вставках может выдать unique_violation — для простого upsert под нагрузкой надёжнее ON CONFLICT. RETURNING в MERGE доступен с PostgreSQL 17.

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

  • Нет уникального индекса на столбцах ON CONFLICT.
  • Upsert пачки, где один ключ встречается дважды, — ошибка «command cannot affect row a second time» (нужно убрать дубли до вставки).
  • Перезаписать в DO UPDATE поля, которые не должны меняться (created_at).
  • Ожидать, что DO NOTHING вернёт существующую строку.

Что важно помнить

  • ON CONFLICT — атомарный upsert в PostgreSQL.
  • EXCLUDED — значения из попытки вставки.
  • Для идемпотентности — DO NOTHING по уникальному ключу события.

Мини-шпаргалка

  • ON CONFLICT (k) DO UPDATE SET c = EXCLUDED.c.
  • ON CONFLICT (k) DO NOTHING.
  • ... DO UPDATE ... WHERE t.v < EXCLUDED.v.
  • MERGE INTO ... USING ... WHEN MATCHED / NOT MATCHED.
  • MySQL: ON DUPLICATE KEY UPDATE.

Документация

PostgreSQL: INSERT ON CONFLICT, PostgreSQL: MERGE.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам