Что такое 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.