UPSERT в SQL: INSERT ON CONFLICT и MERGE без боли

Q: Что такое UPSERT простыми словами?

Представьте таблицу users, куда прилетают обновления профиля. Пользователь с id = 42 мог уже быть, а мог и нет. Без UPSERT мне пришлось бы писать логику на два случая: проверить, есть ли строка, и в зависимости от результата выбрать INSERT или UPDATE. Это две операции, между которыми есть окно, где данные могут измениться, плюс лишний код на стороне приложения. UPSERT схлопывает это в один атомарный шаг. Слово составное: UPdate + inSERT. База сама смотрит, нарушает ли новая строка уникальность п

Q: Чем DO UPDATE отличается от DO NOTHING?

DO UPDATE обновляет строку при конфликте, DO NOTHING — молча пропускает её. Разница принципиальная, и выбор зависит от задачи. DO NOTHING я беру, когда дубли нужно просто отбрасывать. Классика — дедупликация событий по идемпотентному ключу:

Q: Что такое EXCLUDED и как им пользоваться?

EXCLUDED — это псевдотаблица, где лежит строка, которую пытались вставить, но которую «исключили» из-за конфликта. Через неё я достаю значения из входящей строки внутри блока DO UPDATE. Это удобно, потому что не нужно повторять литералы дважды. Сравните: в VALUES я уже написал email и name, и в SET беру их же через EXCLUDED.email, а не копирую строки руками. Особенно это выручает при пакетной вставке многих строк за один запрос — у каждой строки свой EXCLUDED, и SET применится к правильным значе

Q: Как устроен стандартный MERGE?

MERGE — это ответ стандарта SQL на ту же задачу, и он появился раньше, чем ON CONFLICT. Его понимают Oracle, SQL Server, Snowflake, BigQuery, а с версии 15 — и PostgreSQL. Идея другая: не «вставь, а при конфликте сделай то-то», а «сопоставь целевую таблицу с источником и опиши, что делать в каждом случае».

UPSERT — это «вставь строку, а если такая уже есть, обнови её». Одна команда вместо связки «сначала SELECT, потом решаю, делать INSERT или UPDATE». В PostgreSQL это INSERT ... ON CONFLICT, в стандарте SQL и в большинстве коммерческих СУБД — MERGE. Я тянусь к этому каждый раз, когда одну и ту же пачку данных нужно загрузить так, чтобы повторный запуск ничего не сломал. Ниже — как это работает, где подвох с уникальным индексом и параллельными вставками, и чем отличаются диалекты.

Что такое UPSERT простыми словами?

Представьте таблицу users, куда прилетают обновления профиля. Пользователь с id = 42 мог уже быть, а мог и нет. Без UPSERT мне пришлось бы писать логику на два случая: проверить, есть ли строка, и в зависимости от результата выбрать INSERT или UPDATE. Это две операции, между которыми есть окно, где данные могут измениться, плюс лишний код на стороне приложения.

UPSERT схлопывает это в один атомарный шаг. Слово составное: UPdate + inSERT. База сама смотрит, нарушает ли новая строка уникальность по ключу. Нет конфликта — обычная вставка. Есть конфликт — вместо ошибки выполняется то, что я заранее описал: обновить нужные поля или тихо пропустить.

Ключевое слово здесь — «по ключу». UPSERT не работает по воздуху: ему нужен уникальный индекс или ограничение, по которому база понимает, что «такая строка уже есть». Без этого весь механизм рассыпается, и мы к этому ещё вернёмся.

Как работает INSERT ... ON CONFLICT в PostgreSQL?

В Postgres синтаксис читается почти как обычное предложение. Берём вставку и дописываем, что делать при конфликте:

INSERT INTO users (id, email, name, updated_at)
VALUES (42, 'anna@example.com', 'Анна', now())
ON CONFLICT (id) DO UPDATE
SET email = EXCLUDED.email,
    name = EXCLUDED.name,
    updated_at = EXCLUDED.updated_at;

ON CONFLICT (id) — это conflict target, столбец с уникальным ограничением. Если строка с id = 42 уже есть, вместо ошибки duplicate key value выполнится блок DO UPDATE. Если такой строки нет — обычная вставка.

Conflict target может быть составным. Скажем, в таблице daily_metrics уникальна пара «дата плюс метрика»:

INSERT INTO daily_metrics (metric_date, metric_name, value)
VALUES ('2026-07-11', 'dau', 5300)
ON CONFLICT (metric_date, metric_name) DO UPDATE
SET value = EXCLUDED.value;

Ещё можно целиться в конкретное именованное ограничение через ON CONFLICT ON CONSTRAINT uq_users_email или в частичный уникальный индекс, дописав к таргету условие WHERE. Постгрес требователен: имена столбцов в таргете должны в точности совпадать с теми, что покрыты уникальным индексом, иначе он не поймёт, какой именно конфликт ловить. Если хочется пощупать это вживую и посмотреть, как ведёт себя движок, у нас в SQL-тренажёре настоящий PostgreSQL 16 в браузере.

Чем DO UPDATE отличается от DO NOTHING?

DO UPDATE обновляет строку при конфликте, DO NOTHING — молча пропускает её. Разница принципиальная, и выбор зависит от задачи. DO NOTHING я беру, когда дубли нужно просто отбрасывать. Классика — дедупликация событий по идемпотентному ключу:

INSERT INTO events (event_id, user_id, event_type, ts)
VALUES ('evt_abc123', 42, 'purchase', now())
ON CONFLICT (event_id) DO NOTHING;

Если такое событие уже прилетало (например, клиент отправил его дважды из-за ретрая), вторая попытка не даст ошибки и ничего не перезапишет. Первое значение остаётся истиной. Это дешёвый способ гарантировать, что событие учтётся ровно один раз.

DO UPDATE нужен, когда новые данные должны победить старые: свежий статус заказа, актуальный email, пересчитанное значение метрики. У него есть важная деталь — можно добавить WHERE, чтобы обновлять не всегда, а только при выполнении условия:

INSERT INTO orders (order_id, status, amount, updated_at)
VALUES (1001, 'shipped', 4990, now())
ON CONFLICT (order_id) DO UPDATE
SET status = EXCLUDED.status,
    updated_at = EXCLUDED.updated_at
WHERE orders.updated_at < EXCLUDED.updated_at;

Здесь строка обновится, только если пришедшая версия новее той, что уже лежит в таблице. Так я защищаюсь от ситуации, когда запоздавшее сообщение из очереди пытается затереть более свежее состояние. Если условие не выполнено — строка остаётся как есть, ошибки нет.

Что такое EXCLUDED и как им пользоваться?

EXCLUDED — это псевдотаблица, где лежит строка, которую пытались вставить, но которую «исключили» из-за конфликта. Через неё я достаю значения из входящей строки внутри блока DO UPDATE.

Это удобно, потому что не нужно повторять литералы дважды. Сравните: в VALUES я уже написал email и name, и в SET беру их же через EXCLUDED.email, а не копирую строки руками. Особенно это выручает при пакетной вставке многих строк за один запрос — у каждой строки свой EXCLUDED, и SET применится к правильным значениям.

Внутри DO UPDATE доступны обе версии строки: EXCLUDED.column — то, что хотели вставить, а имя таблицы, users.column или orders.column — то, что уже лежит в базе. Это позволяет считать дельту или аккумулировать. Типичный приём — накопительный счётчик:

INSERT INTO page_stats (page, views)
VALUES ('/pricing', 1)
ON CONFLICT (page) DO UPDATE
SET views = page_stats.views + EXCLUDED.views;

При первой вставке views станет 1, при каждом следующем конфликте — прибавится единица к тому, что уже накоплено. Так одной командой ведётся счётчик без отдельного UPDATE.

В SQLite, кстати, работает ровно тот же excluded (в нижнем регистре), синтаксис ON CONFLICT там почти идентичен постгресовому — это наследие общей идеи.

Зачем UPSERT нужен в ETL и идемпотентной загрузке?

Идемпотентность — это когда повторный запуск операции даёт тот же результат, что и первый. Для загрузки данных это спасение: пайплайн может упасть на середине, отработать по расписанию дважды, поймать ретрай из очереди — и таблица останется корректной. UPSERT даёт эту идемпотентность почти бесплатно.

Смотрите на типичный шаг ETL: есть staging-таблица со свежей выгрузкой, и её нужно влить в целевую витрину так, чтобы существующие строки обновились, а новые добавились.

INSERT INTO dim_customers (customer_id, name, segment, updated_at)
SELECT customer_id, name, segment, updated_at
FROM staging_customers
ON CONFLICT (customer_id) DO UPDATE
SET name = EXCLUDED.name,
    segment = EXCLUDED.segment,
    updated_at = EXCLUDED.updated_at;

Запусти я этот шаг один раз или пять — итог один и тот же. Не нужно перед загрузкой чистить таблицу через TRUNCATE, не нужно городить хрупкую логику «удали строки за этот день, потом вставь заново». Дедуп по ключу происходит на уровне СУБД.

Отдельный приятный бонус — RETURNING. Постгрес умеет отдать обратно строки, которые реально изменились, и по ним можно понять, что было вставлено, а что обновлено. Это помогает вести аудит инкрементальной загрузки. Если хочется системно разобраться, как такие пайплайны собираются на практике, посмотрите наши разборы кейсов — там аналитические задачи, где идемпотентная загрузка встречается постоянно. А если UPSERT нужен, чтобы честно посчитать что-то вроде DAU без задвоения событий, дедуп по event_id — ровно то место, где он выручает.

Как устроен стандартный MERGE?

MERGE — это ответ стандарта SQL на ту же задачу, и он появился раньше, чем ON CONFLICT. Его понимают Oracle, SQL Server, Snowflake, BigQuery, а с версии 15 — и PostgreSQL. Идея другая: не «вставь, а при конфликте сделай то-то», а «сопоставь целевую таблицу с источником и опиши, что делать в каждом случае».

MERGE INTO dim_customers AS t
USING staging_customers AS s
ON t.customer_id = s.customer_id
WHEN MATCHED THEN
  UPDATE SET name = s.name,
             segment = s.segment,
             updated_at = s.updated_at
WHEN NOT MATCHED THEN
  INSERT (customer_id, name, segment, updated_at)
  VALUES (s.customer_id, s.name, s.segment, s.updated_at);

MERGE мощнее по выразительности. В нём можно описать не только WHEN MATCHED и WHEN NOT MATCHED, но и удаление строк (WHEN MATCHED THEN DELETE), несколько веток с разными условиями, обработку строк, которых нет в источнике. Для сложной синхронизации двух таблиц он читается яснее, чем нагромождение ON CONFLICT.

Расплата — многословность и коварные грабли. MERGE сопоставляет по произвольному условию в ON, а не по уникальному индексу. И вот тут начинается самое интересное.

Почему параллельные вставки ломают UPSERT?

Потому что UPSERT честен ровно настолько, насколько честен ваш уникальный индекс. Оба механизма опираются на него, но ведут себя под нагрузкой по-разному, и это главный подвох.

INSERT ... ON CONFLICT в Postgres конкурентно безопасен именно за счёт того, что уникальный индекс — арбитр. Когда две транзакции одновременно вставляют строку с одним ключом, движок физически не даст создать два дубля: одна вставит, вторая словит конфликт и уйдёт в DO UPDATE или DO NOTHING. Никто не упадёт с ошибкой уникальности. Это свойство встроено.

С MERGE иначе. Он проверяет условие ON, но между «не нашёл совпадения» и «вставил» есть окно. Если в это окно вторая транзакция уже вставила такую же строку, MERGE пойдёт по ветке WHEN NOT MATCHED и попробует вставить дубль. Дальше два исхода: либо уникальный индекс есть — и транзакция упадёт с unique_violation, либо индекса нет — и вы получите два дубля. MERGE сам по себе не сериализует конкурентные вставки, для этого всё равно нужен уникальный constraint плюс, при высокой конкуренции, ретрай упавшей транзакции.

Отсюда два правила, которые я держу в голове. Первое: под UPSERT всегда должен быть уникальный индекс или ограничение по ключу конфликта — без него ON CONFLICT вообще не скомпилируется, а MERGE тихо наплодит дублей. Второе: если ждёте параллельную нагрузку и вам важна устойчивость, INSERT ... ON CONFLICT в Postgres надёжнее «из коробки», чем MERGE. Такие вопросы про гонки любят задавать на собеседованиях — можно потренироваться на подборке вопросов по SQL.

Чем отличается UPSERT в разных диалектах?

Общая идея одна, синтаксис у всех свой — и это регулярно ловит людей на реальной работе, когда код переносят между базами.

PostgreSQL — INSERT ... ON CONFLICT (target) DO UPDATE / DO NOTHING, входящая строка доступна через EXCLUDED. С версии 15 есть ещё и MERGE.
SQLite — почти копия постгреса: тот же ON CONFLICT и excluded. Плюс исторические короткие формы INSERT OR REPLACE и INSERT OR IGNORE.
MySQL и MariaDB — своя конструкция INSERT ... ON DUPLICATE KEY UPDATE, где новые значения берутся через алиас строки (в свежих версиях) или устаревшую функцию VALUES(). Есть ещё REPLACE INTO, но с ним осторожно: под капотом это удаление старой строки и вставка новой, что триггерит внешние ключи и триггеры, а auto-increment улетает вперёд.
Oracle, SQL Server, Snowflake, BigQuery — стандартный MERGE как основной инструмент.
ClickHouse — привычного UPSERT нет вовсе. Дедупликация делается движком таблиц вроде ReplacingMergeTree, который схлопывает дубли по ключу сортировки в фоне при слиянии кусков. Это не мгновенно и логика совсем другая — держите в голове, если работаете с аналитическим хранилищем.

Практический вывод: перед тем как копировать UPSERT из одного проекта в другой, я всегда сверяюсь, на каком движке он поедет. EXCLUDED из Postgres не заведётся в MySQL, а MERGE из Oracle не всегда дословно ложится на Postgres. Держать эти различия под рукой удобно по SQL-справочнику.

Как закрепить UPSERT на практике?

Коротко: UPSERT — это одна атомарная команда «вставь или обнови по ключу», в Postgres реализованная через INSERT ... ON CONFLICT с псевдотаблицей EXCLUDED, а в стандарте — через MERGE. DO NOTHING пропускает дубли, DO UPDATE перезаписывает, а условие WHERE в блоке обновления защищает от затирания свежих данных устаревшими. Главный фундамент под всем этим — уникальный индекс, без которого механизм либо не работает, либо молча плодит дубли под параллельной нагрузкой. Именно UPSERT делает загрузку в ETL идемпотентной: пайплайн можно перезапускать без страха задвоить строки.

Теория тут дешёвая, а вот руки набиваются только на задачах. Соберите пару UPSERT-запросов в SQL-тренажёре на настоящем PostgreSQL, пройдите базовый курс по SQL, если хочется системы, и порешайте тестовые задания, где UPSERT встречается в связке с оконными функциями и агрегацией. А кто дошёл до Python-части пайплайнов — рядом лежит Python-тренажёр для той же логики загрузки уже на pandas.

Если хотите не разрозненные примеры, а полный доступ ко всем 425 SQL-задачам, разборам кейсов и AI-собеседованиям без лимитов — это открывается в Pro. Но начать можно и с бесплатной части: несколько задач по UPSERT там уже ждут.