Описание

Операция MERGE выполняет действия, которые меняют строки в целевой таблице с именем_целевой_таблицы, используя источник_данных. MERGE — это один SQL-оператор, который по условию выполняет со строками действия INSERT, UPDATE или DELETE; сделать то же самое без MERGE можно, только используя несколько операторов процедурного языка.

Сначала команда MERGE соединяет источник_данных с целевой таблицей, формируя ноль или более строк-кандидатов на изменение. Для каждой строки-кандидата устанавливается неизменяемый позже статус MATCHED (совпадает), NOT MATCHED BY SOURCE (не совпадает по источнику) или NOT MATCHED [BY TARGET]) (не совпадает по цели), после чего вычисляются условия WHEN в заданном порядке. Для каждой отдельной строки будет выполняться действие первого же предложения, условие которого выдаст true. При этом для каждой строки-кандидата может быть выполнено действие не более чем одного предложения WHEN.

Действия операции MERGE имеют тот же эффект, что и обычные одноимённые команды UPDATE, INSERT или DELETE. Синтаксис этих команд в MERGE отличается, в частности, отсутствием предложения WHERE и имени таблицы. Действия этих команд выполняются с целевой таблицей, хотя посредством триггеров могут быть изменены и другие таблицы.

С указанием DO NOTHING исходная строка пропускается. Поскольку применимость действий оценивается в заданном порядке, используя DO NOTHING, удобно пропускать исходные строки, не представляющие интерес, чтобы затем более детально обрабатывать остальные.

Предложение RETURNING указывает, что команда MERGE должна вычислить и вернуть значения для каждой вставленной, изменённой или удалённой строки. Вычислить в нём можно любое выражение со столбцами исходной или целевой таблицы, а также функцию merge_action(). При выполнении команды INSERT или UPDATE используются новые значения столбцов целевой таблицы. При выполнении команды DELETE используются старые значения столбцов целевой таблицы. Список RETURNING имеет тот же синтаксис, что и список результатов SELECT.

Для команды MERGE не предусмотрено отдельное право. Если пользователь указывает в ней действие UPDATE, у него должно быть право UPDATE для столбцов целевой таблицы, на которые ссылается предложение SET. Когда указывается действие INSERT или DELETE, у пользователя должно быть соответствующее право для целевой таблицы. Если пользователь указывает действие DO NOTHING, у него должно быть право SELECT хотя бы для одного столбца целевой таблицы. Кроме того, необходимо иметь право SELECT для любых столбцов источника_данных и целевой таблицы, которые фигурируют в condition (в том числе join_condition) или expression. Права проверяются один раз в начале выполнения оператора, вне зависимости от того, будут ли выполняться конкретные предложения WHEN.

Оператор MERGE не поддерживается для целевых таблиц, являющихся материализованными представлениями, сторонними таблицами, или если для них заданы какие-либо правила.

Параметры

Выводимая информация

При успешном выполнении команда MERGE возвращает метку команды в виде

MERGE общее_число

Здесь общее_число — суммарное количество изменённых строк (добавленных, изменённых или удалённых). Если общее_число равно 0, ни одна строка не была изменена.

Если команда MERGE содержит предложение RETURNING, её результат будет похож на результат SELECT (с теми же столбцами и значениями, что содержатся в списке RETURNING) для строк, добавленных, изменённых или удалённых этой командой.

Примечания

В ходе выполнения MERGE производятся следующие действия.

То есть триггеры уровня оператора для некоторого события (скажем, INSERT) будут вызываться всегда, когда указывается действие такого типа. Триггеры уровня строк, напротив, вызываются только для определённого действия, которое выполняется. Таким образом, при выполнении MERGE могут вызываться триггеры уровня оператора как для UPDATE, так и для INSERT, даже если на уровне строк вызывались только триггеры UPDATE.

Следует позаботиться о том, чтобы для каждой целевой строки в результате соединения создавалось не более одной строки-кандидата на изменение. Другими словами, целевая строка не должна соединяться с более чем одной строкой источника данных. Если это не так, только одна из строк-кандидатов будет применяться для изменения целевой строки; последующие попытки изменить эту строку вызовут ошибку. Ошибка также может произойти, когда триггеры строк вносят изменения в целевую таблицу, а команда MERGE впоследствии воздействует на уже изменённые строки. Если повторится действие INSERT, это вызовет нарушение уникальности, а повторение UPDATE или DELETE вызовет ошибку «Нарушение количества»; последнее требуется стандартом SQL. Такое поведение отличается от поведения соединений в UPDATE и DELETE, традиционного для PostgreSQL, когда вторая и последующие попытки изменить одну и ту же строку просто игнорируются.

Если в предложении WHEN отсутствует дополнительное условие AND, оно становится последним достижимым предложением этого рода (MATCHED, NOT MATCHED BY SOURCE или NOT MATCHED [BY TARGET]). Если в команде встретится последующее предложение WHEN такого рода, оно гарантированно будет недостижимым, и это вызовет ошибку. В случае отсутствия последнего достижимого предложения любого рода возможна ситуация, когда для строки-кандидата на изменение не будет предпринято никаких действий.

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

Когда MERGE выполняется одновременно с другими командами, изменяющими целевую таблицу, применяются обычные правила изоляции транзакций; поведение на каждом уровне изоляции описано в Разделе 13.2. В качестве альтернативы можно рассмотреть использование оператора INSERT ... ON CONFLICT, который предусматривает возможность выполнения команды UPDATE, если параллельно выполняется команда INSERT. Эти два типа операторов имеют ряд различий и особых ограничений, они не являются взаимозаменяемыми.

Примеры

Корректировка клиентских счетов (customer_accounts) с учётом новых транзакций (recent_transactions).

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

Заметьте, что это полностью равнозначно следующему оператору, потому что статус MATCHED не меняется во время выполнения.

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);

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

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta
WHEN MATCHED THEN
  DELETE
RETURNING merge_action(), w.*;

Таблица wine_stock_changes может быть, например, временной таблицей, недавно загруженной в базу данных.

Изменяется wines в соответствии с новым списком, добавляются строки для нового товара, обновляются позиции изменённых товаров и удаляются все виды вин, отсутствующие в новом списке.

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

Совместимость

Эта команда соответствует стандарту SQL.

Предложение WITH, дополнения BY SOURCE и BY TARGET к действиям WHEN NOT MATCHED, DO NOTHING, а также предложение RETURNING являются расширениями стандарта SQL.