UPSERT в SQL: INSERT … ON CONFLICT в PostgreSQL и MERGE

Коротко

Все выводы в статье получены реальными запусками: PostgreSQL 16.14 на временных таблицах и DuckDB 1.5.4 на учебной базе SQL-курса. Движок подписан у каждого результата.

• Upsert — вставка с обновлением при конфликте ключа. В PostgreSQL: insert … on conflict (key) do update set col = excluded.col.
• excluded — строка, которую вы пытались вставить. Имя целевой таблицы в do update — строка, которая уже лежит в базе.
• Конфликт определяется только по уникальному ограничению или уникальному индексу. Без него запрос падает с ошибкой.
• do nothing оставляет существующую строку как есть, do update перезаписывает выбранные колонки.
• where в do update пропускает строки, которые не изменились, — меньше лишних записей.
• merge (PostgreSQL 15+, стандарт SQL) гибче, но при параллельной вставке того же ключа может упасть с duplicate key, а on conflict — нет.

Зачем аналитику upsert, если можно просто вставить строки?

Потому что загрузки перезапускаются. Задача падает, приходят опоздавшие события, кто-то чинит расчёт метрики и пересчитывает месяц. Если загрузка при повторном запуске дописывает строки, каждый перезапуск портит витрину. Свойство «сколько раз ни запусти — результат один» называется идемпотентностью, и для витрин это базовое требование.

На учебной базе в DuckDB разница видна сразу. Витрина за 1–7 июня — 7 строк и 748 событий. Та же загрузка обычным insert дважды подряд даёт 14 строк и 1496 событий. Через on conflict второй запуск оставляет 7 строк и 748 событий: каждый день нашёл свою строку и обновил её.

Есть два способа добиться идемпотентности. Первый — удалить период и вставить его заново в одной транзакции. Второй — upsert по ключу. Удаление с перезаливкой проще, когда пересчитывается весь период целиком. Upsert удобнее, когда строки приходят по частям, из нескольких источников или когда у строки есть поля, которые загрузка трогать не должна: дата создания, ручная пометка, комментарий.

Как работает INSERT … ON CONFLICT DO UPDATE

Запрос состоит из обычной вставки и хвоста, который говорит, что делать при конфликте. В скобках после on conflict — колонки, по которым ищется совпадение. После do update set — какие колонки обновить и чем.

Внутри do update доступны две строки. excluded.dau — значение из вставки, которая не прошла. daily_metrics.dau — значение, которое уже лежит в таблице. Их можно комбинировать: set dau = daily_metrics.dau + excluded.dau превращает upsert в накопительный счётчик. На PostgreSQL такая вставка значения 3 поверх существующего 1 вернула 4.

Ниже загрузка витрины из сырых событий. Окно задаётся полуоткрытым интервалом, агрегат считается в select, а конфликт по дню решается обновлением. Колонку updated_at полезно обновлять явно: по ней потом видно, когда строку трогали в последний раз.

create table daily_metrics (
  day        date primary key,
  dau        int not null,
  events     int not null,
  updated_at timestamp not null default now()
);

insert into daily_metrics (day, dau, events)
select
  event_time::date,
  count(distinct user_id),
  count(*)
from events
where event_time >= '2026-06-01'
  and event_time <  '2026-06-04'
group by 1
on conflict (day) do update
set dau        = excluded.dau,
    events     = excluded.events,
    updated_at = now();

Почему ON CONFLICT требует уникальный индекс?

Конфликт — это нарушение уникальности. База узнаёт о нём только из уникального ограничения: primary key, unique или уникального индекса. Если на колонке day ничего такого нет, двух одинаковых дней в таблице быть может сколько угодно, и конфликтовать не с чем. PostgreSQL в этом случае не выполняет запрос вовсе: «there is no unique or exclusion constraint matching the ON CONFLICT specification».

Список колонок в on conflict (…) должен совпадать с каким-то уникальным ключом целиком. Если витрина хранит строку на день и канал, ключ — unique (day, channel), и в on conflict пишется (day, channel). Вместо колонок можно назвать ограничение: on conflict on constraint daily_metrics_pkey.

Отдельная ловушка — NULL в ключе. Для уникального индекса два NULL не равны друг другу, поэтому строка с пустым каналом никогда не конфликтует. На PostgreSQL две одинаковые вставки ('2026-06-01', null) в таблицу с unique (day, channel) дали две строки. В учебной базе у 55 пользователей не указана страна, и витрина по дням и странам копила бы строку с пустой страной при каждом перезапуске. Выхода два: заменить NULL на значение вроде 'unknown' до вставки или объявить ключ как unique nulls not distinct (day, channel) — это есть с PostgreSQL 15. Со вторым вариантом повторная вставка обновила строку, а не добавила новую.

Что делать с ошибкой «cannot affect row a second time»

Одна команда insert … on conflict do update не может обновить одну и ту же строку дважды. Если в данных для вставки один ключ встречается два раза, PostgreSQL останавливается: «ON CONFLICT DO UPDATE command cannot affect row a second time». Непонятно, какую из двух версий считать итоговой, и база не выбирает за вас.

В аналитических загрузках это почти всегда признак ошибки в агрегате: забыли колонку в group by, склеили два источника через union all, в сырых данных пришёл дубль. Лечится это до вставки. Сгруппируйте по ключу витрины или выберите одну строку на ключ через row_number(), и только потом отдавайте результат в upsert. do nothing на такие дубли не ругается: первая строка вставится, вторая будет молча пропущена, и ошибка в данных останется незамеченной.

DO NOTHING или DO UPDATE: что выбрать?

do nothing подходит, когда строка после первой записи не должна меняться: журнал обработанных файлов, справочник, который пополняется новыми значениями, первое появление пользователя. На PostgreSQL вставка ('2026-06-01', 999, 999) поверх существующего дня с do nothing вернула «INSERT 0 0», и в таблице остались прежние 2 пользователя и 3 события.

do update нужен, когда значение может уточниться: метрики за день, статус заказа, последний визит. Для витрин это вариант по умолчанию, потому что опоздавшие события меняют уже посчитанные дни.

Как обновлять только изменившиеся строки?

Каждый update в PostgreSQL создаёт новую версию строки, даже если значения те же. Для витрины, которую каждую ночь перезаливают за 30 дней, это лишние перезаписи и сдвинутый updated_at, по которому уже не понять, менялись ли данные.

Условие where после set пропускает такие строки. Сравнивать удобно через is distinct from: в отличие от <>, он корректно сравнивает NULL. На PostgreSQL повторный запуск загрузки за три дня с таким условием вернул 0 строк в returning: все три дня уже были актуальны, и ни одна строка не переписана.

insert into daily_metrics (day, dau, events)
select event_time::date, count(distinct user_id), count(*)
from events
where event_time >= '2026-06-01'
  and event_time <  '2026-06-04'
group by 1
on conflict (day) do update
set dau        = excluded.dau,
    events     = excluded.events,
    updated_at = now()
where (daily_metrics.dau, daily_metrics.events)
      is distinct from (excluded.dau, excluded.events);

Как посчитать, сколько строк вставлено, а сколько обновлено?

PostgreSQL в ответ на upsert пишет только общее число: «INSERT 0 3». Для лога загрузки этого мало: хочется знать, сколько дней появилось впервые и сколько пересчитано. Распространённый приём — добавить returning (xmax = 0) as inserted. У только что вставленной строки системная колонка xmax равна нулю, у обновлённой — нет.

На PostgreSQL 16 первый запуск за 1–2 июня вернул inserted = t для обеих строк. Потом в сырые данные дописали опоздавшее событие за 2 июня и первое событие 3 июня. Второй запуск за 1–3 июня вернул f, f, t: два дня обновлены, один вставлен. DAU за 2 июня при этом вырос с 2 до 3 — ровно тот случай, ради которого витрину перезаливают с запасом.

Имейте в виду, что xmax — деталь внутреннего устройства PostgreSQL, а не документированный интерфейс upsert. Приём работает годами, но для строгих логов надёжнее хранить в таблице created_at и сравнивать его с updated_at.

Как загружать витрину инкрементально

Пересчитывать всю историю каждую ночь дорого, а пересчитывать только вчера опасно: события приходят с опозданием, мобильные клиенты отправляют пачки после восстановления сети. Рабочий компромисс — окно перезагрузки. Каждый запуск пересчитывает последние N дней и upsert-ом кладёт их в витрину. N выбирают по данным: посмотрите, через сколько дней после event_time доезжает 99% событий.

На учебной базе в DuckDB это выглядит так. Первый запуск загрузил 1–7 июня. Следующий запуск взял окно 5–14 июня: returning day вернул 10 строк, из них три дня пересчитаны, семь добавлены, а в витрине стало 14 дней и 1923 события. Пересекающиеся окна не создают дублей, потому что каждый день существует в витрине ровно один раз.

У окна есть обратная сторона: день, который выпал из окна, больше не обновляется. Если источник исправили задним числом за прошлый месяц, нужен отдельный ручной пересчёт этого периода — той же командой, с другими границами.

insert into daily_metrics
select event_time::date, count(distinct user_id), count(*)
from events
where event_time >= '2026-06-05'
  and event_time <  '2026-06-15'
group by 1
on conflict (day) do update
set dau = excluded.dau, events = excluded.events
returning day;

Что будет, если две загрузки запустятся одновременно?

Частый самодельный upsert выглядит так: проверить через select, есть ли строка, и в зависимости от ответа сделать insert или update. Между проверкой и вставкой другая сессия успевает вставить ту же строку, и одна из загрузок падает. on conflict делает проверку и вставку атомарно: если параллельная транзакция уже вставила ключ, PostgreSQL дождётся её завершения и пойдёт по ветке do update.

Это проверено на двух сессиях PostgreSQL 16.14. Первая сессия открывает транзакцию, вставляет день со значением 10 и три секунды не фиксирует. Вторая в это время пытается записать тот же день со значением 20 тремя способами.

Чем MERGE отличается от ON CONFLICT?

merge — команда из стандарта SQL, в PostgreSQL она есть с 15-й версии. Она сопоставляет целевую таблицу с источником по произвольному условию и для каждого случая задаёт действие: обновить, вставить, удалить или ничего не делать. Условий when может быть несколько, и у каждого может быть своё дополнительное условие.

На PostgreSQL merge из staging-таблицы в три строки, где один день совпадал, один изменился и один был новым, вернул «MERGE 2»: одна строка обновлена, одна вставлена, неизменившаяся пропущена условием when matched and … is distinct from …. DuckDB 1.5.4 на учебной базе тоже выполняет merge into.

Главное отличие для витрин — поведение при гонке. merge не требует уникального ключа: он ищет совпадения по условию on. Поэтому в таблицу без ключа он вставил все три строки, ни на что не пожаловавшись, а при параллельной вставке того же ключа упал с duplicate key, как показано выше. Документация PostgreSQL прямо советует insert … on conflict, если нужно гарантированно обновить строку при конкурентной вставке. Практическое правило: одна загрузка в таблицу и сложная логика с удалениями — merge, параллельные писатели и простой upsert по ключу — on conflict. В PostgreSQL 17 у merge появились returning с функцией merge_action() и ветка when not matched by source для удаления строк, которых нет в источнике.

merge into daily_metrics as t
using staging as s
on t.day = s.day
when matched
 and (t.dau, t.events) is distinct from (s.dau, s.events) then
  update set dau = s.dau, events = s.events
when not matched then
  insert (day, dau, events) values (s.day, s.dau, s.events);

Как upsert пишется в других базах?

Синтаксис не стандартизован, кроме merge, поэтому в каждой базе свой вариант. SQLite поддерживает тот же on conflict (…) do update set … = excluded.…, что и PostgreSQL. DuckDB тоже, плюс короткую форму insert or replace, которая заменяет строку целиком. В MySQL конструкция называется insert … on duplicate key update и срабатывает на любом уникальном ключе таблицы, без явного указания колонок.

В колоночных хранилищах upsert часто устроен иначе. В ClickHouse таблица на движке ReplacingMergeTree схлопывает версии строки только при фоновых слияниях, и до слияния запрос может увидеть дубли.

Какие ещё сюрпризы бывают у upsert?

Счётчик serial расходуется даже на обновлённых строках. На PostgreSQL после вставки двух дней и upsert-а тех же двух дней следующая новая строка получила id = 5, а не 3. Дыры в суррогатных ключах нормальны, но не используйте такой id как порядковый номер или счётчик строк.

Upsert не удаляет. Если день пропал из источника, например после чистки тестовых пользователей, строка в витрине останется со старыми цифрами. Для таких случаев подходит перезаливка периода через delete и insert в одной транзакции или merge с веткой удаления.

Ключ витрины должен совпадать с зерном агрегата. Если витрина по дням, а group by по дню и платформе, в upsert придёт несколько строк на один день, и PostgreSQL остановится с ошибкой «cannot affect row a second time». Это полезная ошибка: она ловит несовпадение зерна раньше, чем цифры попадут в отчёт.

Проверьте на учебной базе

Песочница SQL-курса выполняет только select, поэтому сам upsert там не запустить. Зато можно подготовить то, что пойдёт в загрузку: посчитайте DAU и события по дням за 1–14 июня и проверьте, что на каждый день приходится ровно одна строка, — group by day having count(*) > 1 должен вернуть пустой результат. Затем посчитайте то же по дням и странам и посмотрите, сколько строк приходится на пустую страну: именно они при upsert без nulls not distinct размножились бы.