Что такое upsert через ON CONFLICT DO UPDATE в PostgreSQL и чем отличается от простого UPSERT?
Что происходит при конфликте
INSERT ... VALUES (...) ON CONFLICT (столбец) DO UPDATE SET ... — PostgreSQL пытается вставить новую строку обычным способом. Если вставка нарушает уникальный индекс или ограничение (UNIQUE/PRIMARY KEY), указанное в ON CONFLICT, вместо ошибки выполняется UPDATE этой же строки в рамках той же команды и той же транзакции. Операция полностью атомарна на уровне СУБД: конкурентные вставки с одинаковым ключом не приводят к состоянию гонки и не требуют дополнительных блокировок со стороны приложения.
Синтаксис и EXCLUDED
В блоке DO UPDATE SET доступна псевдотаблица EXCLUDED — она содержит значения, которые пытались вставить:
INSERT INTO users (email, name, last_seen)
VALUES ('a@test.com', 'Anna', now())
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name, last_seen = EXCLUDED.last_seen;
Также можно указать конфликтующее ограничение явно через ON CONSTRAINT имя_constraint, если конфликт нужно ловить не по индексу столбцов, а по имени constraint (полезно, если уникальных ограничений несколько).
DO NOTHING vs DO UPDATE
ON CONFLICT DO NOTHING просто игнорирует конфликтующую строку — команда завершится успешно, но данные не изменятся. DO UPDATE обязательно требует указания цели конфликта ((столбец) или ON CONSTRAINT), потому что PostgreSQL должен точно знать, какую строку обновлять.
Условный upsert (WHERE)
Обновление можно ограничить условием, чтобы не перезаписывать строку впустую:
INSERT INTO products (sku, price, updated_at)
VALUES ('SKU-1', 999, now())
ON CONFLICT (sku)
DO UPDATE SET price = EXCLUDED.price, updated_at = now()
WHERE products.price IS DISTINCT FROM EXCLUDED.price;
Это снижает write amplification и не плодит лишние версии строк (MVCC bloat), не трогая индексы и триггеры без необходимости.
Почему это лучше «наивного» upsert
Под «простым upsert» обычно понимают ручной паттерн: сначала SELECT для проверки существования строки, затем в зависимости от результата — INSERT или UPDATE отдельными запросами (иногда это оборачивают в PL/pgSQL-функцию с EXCEPTION WHEN unique_violation). Проблемы такого подхода:
- Race condition — между
SELECTиINSERTдругая транзакция может вставить ту же строку, и второйINSERTупадёт с ошибкой уникальности. - Нужны дополнительные round-trip'ы к БД или явная блокировка (
SELECT ... FOR UPDATE, повышение уровня изоляции доSERIALIZABLE), что снижает пропускную способность. - Логика размазана по приложению или требует отдельной хранимой процедуры с обработкой исключений, что медленнее нативного
ON CONFLICT.
ON CONFLICT DO UPDATE решает всё это одной атомарной командой на стороне сервера БД.
MERGE как альтернатива
Начиная с PostgreSQL 15 доступен стандартный SQL MERGE, который умеет выполнять несколько разных действий по условиям (WHEN MATCHED, WHEN NOT MATCHED) и подходит для сложных сценариев синхронизации таблиц. Но для типового upsert по одному ключу ON CONFLICT остаётся проще, компактнее и обычно быстрее.
Что хочет услышать интервьюер
Понимание, что ON CONFLICT DO UPDATE — это атомарная операция в рамках одной команды INSERT, а не два отдельных запроса
Знание синтаксиса и псевдотаблицы EXCLUDED
Умение объяснить разницу между ON CONFLICT DO NOTHING и DO UPDATE
Понимание, что для ON CONFLICT нужен существующий UNIQUE/PRIMARY KEY индекс, на который можно сослаться
Способность объяснить race condition в наивном варианте upsert (SELECT + INSERT/UPDATE) и почему нативный upsert его устраняет
Пример: Базовый upsert с EXCLUDED
INSERT INTO users (email, name, last_seen)
VALUES ('a@test.com', 'Anna', now())
ON CONFLICT (email)
DO UPDATE SET
name = EXCLUDED.name,
last_seen = EXCLUDED.last_seen;
Пример: Условный upsert, чтобы не обновлять без изменений
INSERT INTO products (sku, price, updated_at)
VALUES ('SKU-1', 999, now())
ON CONFLICT (sku)
DO UPDATE SET
price = EXCLUDED.price,
updated_at = now()
WHERE products.price IS DISTINCT FROM EXCLUDED.price;
Пример: Наивный upsert — подвержен гонке
-- Так делать не стоит: между SELECT и INSERT/UPDATE
-- другая транзакция может вставить ту же строку
SELECT 1 FROM users WHERE email = 'a@test.com';
-- если не найдено:
INSERT INTO users (email, name) VALUES ('a@test.com', 'Anna');
-- если найдено:
UPDATE users SET name = 'Anna' WHERE email = 'a@test.com';
Типичные ошибки
Путают ON CONFLICT DO UPDATE с обычным UPDATE ... WHERE, не понимая, что это единая атомарная команда
Забывают, что ON CONFLICT требует существующего уникального индекса или constraint — без него PostgreSQL выдаст ошибку
Не добавляют условие WHERE в DO UPDATE, из-за чего строка обновляется даже когда данные идентичны, что увеличивает MVCC bloat
Считают «простой UPSERT» отдельной SQL-командой стандарта, хотя это лишь общий паттерн, который в PostgreSQL без ON CONFLICT приходится реализовывать вручную через несколько запросов
Не знают про ON CONSTRAINT и не могут объяснить, как разрешить конфликт, если уникальных ограничений на таблице несколько
AI-тренажеры
4.7
Практика в студии
