MERGE in PostgreSQL 15+: MATCHED / NOT MATCHED, Upserts, and Table Sync

MERGE — это один оператор, который умеет за один проход сделать INSERT, UPDATE и DELETE в зависимости от того, нашлась ли строка в целевой таблице. До PostgreSQL 15 его не было, и все жили на INSERT ... ON CONFLICT. Теперь у нас есть честный SQL-стандартный MERGE, и он закрывает сценарии, которые ON CONFLICT в принципе не вытягивает: синхронизацию двух таблиц, удаление лишнего, разные действия на разных условиях.

Разберёмся, как он устроен, и где проходит граница между MERGE и ON CONFLICT.

Анатомия: target, source и ветки WHEN

MERGE берёт целевую таблицу (MERGE INTO), источник (USING) и условие соединения (ON). Дальше идут ветки WHEN MATCHED (строка совпала по ON) и WHEN NOT MATCHED (в источнике есть, в цели нет).

Возьмём схему: есть таблица users и приходящий батч свежих данных users_staging.

MERGE INTO users AS u
USING users_staging AS s
   ON u.id = s.id
WHEN MATCHED THEN
  UPDATE SET email = s.email,
             name  = s.name,
             updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (id, email, name, created_at)
  VALUES (s.id, s.email, s.name, now());

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

• Источником может быть таблица, подзапрос или VALUES.
• Ветки проверяются сверху вниз; срабатывает первая подходящая.
• Внутри ветки нельзя ссылаться на EXCLUDED — это синтаксис ON CONFLICT, в MERGE его нет. Просто пишем s.email, s.name.
• MERGE не возвращает строки в старых версиях; RETURNING появился только в PostgreSQL 17.

Условные ветки и DELETE

Сила MERGE — в дополнительных условиях AND на каждой ветке и в том, что веток может быть несколько. Это уже невозможно повторить через ON CONFLICT.

MERGE INTO orders AS o
USING incoming_orders AS i
   ON o.order_id = i.order_id
WHEN MATCHED AND i.status = 'cancelled' THEN
  DELETE
WHEN MATCHED AND o.amount <> i.amount THEN
  UPDATE SET amount = i.amount,
             status = i.status,
             updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, status)
  VALUES (i.order_id, i.customer_id, i.amount, i.status);

Здесь три разных сценария в одном запросе: отменённый заказ удаляем, изменившийся — обновляем (и только если сумма реально поменялась), нового — вставляем. Можно добавить и WHEN NOT MATCHED ... AND ..., чтобы фильтровать вставки.

Полезные приёмы:

• WHEN MATCHED AND ... THEN DO NOTHING — явно пропустить строку.
• WHEN NOT MATCHED BY SOURCE THEN DELETE — удалить из цели то, чего нет в источнике (это уже PostgreSQL 17; идеально для полной синхронизации).
• Порядок веток = порядок приоритета. Узкие условия ставьте выше широких.

Полная синхронизация двух таблиц

Классическая задача: привести таблицу employees к состоянию выгрузки из HR-системы hr_feed — добавить новых, обновить изменившихся, уволить отсутствующих.

MERGE INTO employees AS e
USING hr_feed AS f
   ON e.emp_id = f.emp_id
WHEN MATCHED AND (e.department, e.salary) IS DISTINCT FROM (f.department, f.salary) THEN
  UPDATE SET department = f.department,
             salary     = f.salary
WHEN NOT MATCHED THEN
  INSERT (emp_id, full_name, department, salary)
  VALUES (f.emp_id, f.full_name, f.department, f.salary)
WHEN NOT MATCHED BY SOURCE THEN
  UPDATE SET status = 'terminated';

IS DISTINCT FROM тут защищает от лишних UPDATE, когда данные не поменялись (и корректно сравнивает NULL). А NOT MATCHED BY SOURCE закрывает третий угол — то, что осталось только в цели.

В MySQL отдельного MERGE нет — используют INSERT ... ON DUPLICATE KEY UPDATE. В ClickHouse подход другой: вставка плюс ReplacingMergeTree/ENGINE-логика, дедупликация откладывается до фонового слияния.

MERGE против ON CONFLICT: что выбрать

Оба умеют upsert, но решают его по-разному.

• ON CONFLICT работает через нарушение уникального индекса или ограничения. Нет подходящего индекса — нет upsert.
• MERGE работает через произвольное условие ON. Индекс не обязателен (хотя для скорости желателен).
• ON CONFLICT атомарен относительно конкурентных вставок: при гонке он надёжно ловит конфликт. MERGE под высокой конкуренцией может упасть с ошибкой уникальности — он не делает skip-locked магии.
• MERGE умеет DELETE и несколько разных веток; ON CONFLICT — только «вставь или обнови одну строку».

Практическое правило:

-- Простой счётчик / идемпотентная вставка — берите ON CONFLICT
INSERT INTO page_views (page_id, views)
VALUES (42, 1)
ON CONFLICT (page_id)
DO UPDATE SET views = page_views.views + 1;

Для горячего конкурентного upsert по одному ключу ON CONFLICT проще и безопаснее. MERGE берите, когда нужна логика «несколько веток + DELETE + синхронизация», то есть для пакетной обработки и ETL.

Подводный камень. MERGE не защищает от гонок сам по себе. Если два сеанса одновременно мёржат один ключ, которого ещё нет, оба пойдут в ветку NOT MATCHED и один словит unique_violation. Для конкурентной нагрузки оборачивайте в retry или используйте ON CONFLICT. И помните: RETURNING в MERGE доступен только с PostgreSQL 17.