MERGE
MERGE — добавить, изменить или удалить строки таблицы по условию
Синтаксис
[ WITHзапрос_WITH
[, ...] ] MERGE INTO [ ONLY ]имя_целевой_таблицы
[ * ] [ [ AS ]целевой_псевдоним
] USINGисточник_данных
ONусловие_соединения
предложение_when
[...] [ RETURNING { * |выражение_результата
[ [ AS ]имя_результата
] } [, ...] ] Здесьисточник_данных
: { [ ONLY ]имя_исходной_таблицы
[ * ] | (исходный_запрос
) } [ [ AS ]исходный_псевдоним
] ипредложение_when
: { WHEN MATCHED [ ANDусловие
] THEN {изменение_при_объединении
|удаление_при_объединении
| DO NOTHING } | WHEN NOT MATCHED BY SOURCE [ ANDусловие
] THEN {изменение_при_объединении
|удаление_при_объединении
| DO NOTHING } | WHEN NOT MATCHED [ BY TARGET ] [ ANDусловие
] THEN {добавление_при_объединении
| DO NOTHING } } идобавление_при_объединении
: INSERT [(имя_столбца
[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {выражение
| DEFAULT } [, ...] ) | DEFAULT VALUES } иизменение_при_объединении
: UPDATE SET {имя_столбца
= {выражение
| DEFAULT } | (имя_столбца
[, ...] ) = [ ROW ] ( {выражение
| DEFAULT } [, ...] ) | (имя_столбца
[, ...] ) = (вложенный_SELECT
) } [, ...] иудаление_при_объединении
: DELETE
Описание
Операция 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
не поддерживается для целевых таблиц, являющихся материализованными представлениями, сторонними таблицами, или если для них заданы какие-либо правила.
Параметры
запрос_WITH
Предложение
WITH
позволяет задать один или несколько подзапросов, на которые затем можно ссылаться по имени в запросеMERGE
. За подробностями обратитесь к Разделу 7.8 и SELECT. Обратите внимание, что предложениеWITH RECURSIVE
для командыMERGE
не поддерживается.имя_целевой_таблицы
Имя (возможно, дополненное схемой) целевой таблицы или представления, которые принимают результат объединения. Если перед именем таблицы добавлено
ONLY
, соответствующие строки изменяются или удаляются только в указанной таблице. БезONLY
соответствующие строки также изменяются или удаляются во всех таблицах, унаследованных от указанной таблицы. При желании, после имени таблицы можно указать*
, чтобы явно обозначить, что операция затрагивает все дочерние таблицы. Ключевое словоONLY
и параметр*
не влияют на действияINSERT
, добавляющие строки только в указанную таблицу.Если в
имени_целевой_таблицы
указано имя представления, оно должно быть либо автоматически изменено без триггеровINSTEAD OF
, либо иметь триггерыINSTEAD OF
для каждого действия (INSERT
,UPDATE
иDELETE
), указанного в предложенииWHEN
. Представления, для которых заданы правила, не поддерживаются.целевой_псевдоним
Альтернативное имя целевой таблицы. Когда это имя задаётся, настоящее имя таблицы полностью скрывается. Например, в запросе
MERGE INTO foo AS f
остальные компоненты оператораMERGE
должны обращаться к целевой таблице по имениf
, а неfoo
.имя_исходной_таблицы
Имя (возможно, дополненное схемой) исходной таблицы, представления или переходной таблицы. Если перед именем таблицы добавлено
ONLY
, соответствующие строки берутся только из указанной таблицы. БезONLY
строки также берутся из всех таблиц, унаследованных от указанной. При желании, после имени таблицы можно указать*
, чтобы явно обозначить, что операция затрагивает все дочерние таблицы.исходный_запрос
Запрос (оператор
SELECT
или операторVALUES
), предоставляющий строки для объединения в целевой таблице. За информацией о синтаксисе обратитесь к описанию SELECT и VALUES.исходный_псевдоним
Альтернативное имя для источника данных. Когда задаётся этот псевдоним, он полностью скрывает настоящее имя таблицы или тот факт, что это результат запроса.
условие_соединения
Задаваемое
условие_соединения
представляет собой выражение, выдающее значение типаboolean
(как в предложенииWHERE
), которое определяет, какие строки висточнике_данных
соответствуют строкам в целевой таблице.Предупреждение
В
условии_соединения
должны фигурировать только столбцы целевой таблицы, по которым её строки сопоставляются со строкамиисточника_данных
. Подвыраженияусловия_соединения
, ссылающиеся только на столбцы целевой таблицы, могут влиять на выполняемое действие, часто неожиданным образом.Если указаны оба предложения
WHEN NOT MATCHED BY SOURCE
иWHEN NOT MATCHED [BY TARGET]
, командаMERGE
сделает полное соединение (FULL JOIN
)источника_данных
с целевой таблицей. Для корректной работы необходимо, чтобы хотя бы в одном подвыраженииусловия_соединения
использовался оператор с поддержкой соединений по хешу, или чтобы во всех подвыражениях использовались операторы с поддержкой соединений слиянием.предложение_when
В команде
MERGE
должно быть минимум одно предложениеWHEN
.В предложении
WHEN
можно задатьWHEN MATCHED
,WHEN NOT MATCHED BY SOURCE
илиWHEN NOT MATCHED [BY TARGET]
. Обратите внимание, что стандарт SQL определяет толькоWHEN MATCHED
иWHEN NOT MATCHED
(отсутствие соответствующей целевой строки).WHEN NOT MATCHED BY SOURCE
является расширением стандарта SQL как способ совместно использоватьBY TARGET
иWHEN NOT MATCHED
для более точного запроса.Если в предложении
WHEN
указаноWHEN MATCHED
и строка-кандидат на изменение представляет собой строку изисточника_данных
, совпадающую со строкой целевой таблицы, то предложениеWHEN
выполняется, когдаусловие
отсутствует или оценивается какtrue
.И наоборот, если в предложении
WHEN
указаноWHEN NOT MATCHED BY SOURCE
и строка-кандидат на изменение является строкой целевой таблицы, которая не соответствует строке висточнике_данных
, предложениеWHEN
выполняется, когдаусловие
отсутствует или оценивается какtrue
.Если в предложении
WHEN
указаноWHEN NOT MATCHED [BY TARGET]
и строка-кандидат на изменение является строкой висточнике_данных
, которая не соответствует строке целевой таблицы, предложениеWHEN
выполняется, когдаусловие
отсутствует или оценивается какtrue
.условие
Выражение, выдающее значение типа
boolean
. Если это выражение для предложенияWHEN
выдаётtrue
, для данной строки выполняется действие этого предложения.Условие в предложении
WHEN MATCHED
может ссылаться на столбцы как исходного, так и целевого отношения. Условие в предложенииWHEN NOT MATCHED BY SOURCE
может ссылаться только на столбцы целевого отношения, поскольку соответствующей исходной строки нет по определению. Условие в предложенииWHEN NOT MATCHED [BY TARGET]
может ссылаться только на столбцы исходного отношения, поскольку соответствующей целевой строки нет по определению. В целевой таблице доступны только системные атрибуты.добавление_при_объединении
Указание действия
INSERT
, добавляющего одну строку в целевую таблицу. Имена целевых столбцов могут перечисляться в любом порядке. Если список имён столбцов не задан вовсе, по умолчанию используются все столбцы таблицы в порядке объявления.Все столбцы, не представленные в явном или неявном списке столбцов, получат значения по умолчанию, если для них заданы эти значения, либо NULL в противном случае.
Если целевая таблица является секционированной, каждая строка направляется в соответствующую секцию и добавляется в неё. Если целевая таблица является секцией и какая-либо входная строка нарушит ограничение секции, произойдёт ошибка.
Имена столбцов нельзя указывать более одного раза. Действия
INSERT
не могут содержать вложенные запросыSELECT
.Предложение
VALUES
может указываться только один раз. Ссылаться в нём можно только на столбцы исходного отношения, так как соответствующих целевых строк нет по определению.изменение_при_объединении
Указание действия
UPDATE
, изменяющего текущую строку целевой таблицы. Имена столбцов нельзя указывать более одного раза.Задавать имя таблицы и предложение
WHERE
здесь нельзя.удаление_при_объединении
Указание действия
DELETE
, удаляющего текущую строку целевой таблицы. Задавать имя таблицы или какие-либо другие предложения, как в обычной команде DELETE, здесь нельзя.имя_столбца
Имя столбца целевой таблицы. При необходимости имя столбца можно дополнить именем поля или индексом массива. (При добавлении данных лишь в некоторые поля составного типа другие поля будут содержать NULL.) Имя таблицы в указание целевого столбца добавлять не нужно.
OVERRIDING SYSTEM VALUE
Без этого предложения не допускается задание явного значения (отличного от
DEFAULT
) для столбца идентификации, определённого с характеристикойGENERATED ALWAYS
. Данное предложение перекрывает это ограничение.OVERRIDING USER VALUE
Если указывается это предложение, то значения, заданные для столбцов идентификации, которые определены с характеристикой
GENERATED BY DEFAULT
, игнорируются и вместо них применяются значения, выдаваемые последовательностями по умолчанию.DEFAULT VALUES
Все столбцы получают значения по умолчанию. (Предложение
OVERRIDING
в этой форме не допускается.)выражение
Выражение, результат которого присваивается столбцу. В выражениях предложений
WHEN MATCHED
могут использоваться значения из исходной строки целевой таблицы и значения из строкиисточника_данных
. В выражениях предложенийWHEN NOT MATCHED BY SOURCE
могут использоваться значения только из исходной строки в целевой таблице. В выражениях предложенийWHEN NOT MATCHED [BY TARGET]
могут использоваться значения только изисточника_данных
.DEFAULT
Присвоить столбцу значение по умолчанию (или
NULL
, если выражение по умолчанию для столбца не определено).вложенный_SELECT
Подзапрос
SELECT
, выдающий столько выходных столбцов, сколько перечислено в предшествующем ему списке столбцов в скобках. При выполнении этого подзапроса должна быть получена максимум одна строка. Если он выдаёт одну строку, значения столбцов в нём присваиваются целевым столбцам; если же он не возвращает строку, целевым столбцам присваивается NULL. При использовании предложенияWHEN MATCHED
подзапрос может обращаться к значениям исходной строки в целевой таблице, а также значениям строкиисточника_данных
. При использовании предложенияWHEN NOT MATCHED BY SOURCE
подзапрос может обращаться только к значениям исходной строки в целевой таблице.выражение_результата
Выражение, вычисляемое и возвращаемое командой
MERGE
после изменения каждой строки (добавления, изменения или удаления). В этом выражении можно использовать имена любых столбцов исходной и целевой таблицы или функциюmerge_action()
для получения дополнительной информации о выполняемых действиях.С указанием
*
возвращаются все столбцы исходной таблицы, а после них — все столбцы целевой. Обычно это приводит к большому количеству дубликатов, поскольку у исходной и целевой таблицы часто одинаковые столбцы. Этого можно избежать, указав при использовании*
имена или псевдонимы исходной или целевой таблицы.имя_результата
Имя, назначаемое возвращаемому столбцу.
Выводимая информация
При успешном выполнении команда MERGE
возвращает метку команды в виде
MERGE общее_число
Здесь общее_число
— суммарное количество изменённых строк (добавленных, изменённых или удалённых). Если общее_число
равно 0, ни одна строка не была изменена.
Если команда MERGE
содержит предложение RETURNING
, её результат будет похож на результат SELECT
(с теми же столбцами и значениями, что содержатся в списке RETURNING
) для строк, добавленных, изменённых или удалённых этой командой.
Примечания
В ходе выполнения MERGE
производятся следующие действия.
Вызываются все триггеры
BEFORE STATEMENT
для всех указанных действий, независимо от того, совпадают ли их предложенияWHEN
.Выполняется соединение исходной таблицы с целевой. Полученный в результате запрос оптимизируется как обычно и выдаёт набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:
Для каждой строки определяется состояние:
MATCHED
(совпадает),NOT MATCHED BY SOURCE
(не совпадает по источнику) илиNOT MATCHED [BY TARGET]
(не совпадает по цели).Проверяется каждое условие
WHEN
в заданном порядке, пока какое-либо не выдаст значение true.Если условие оценивается как
true
, происходит следующее:Вызываются все триггеры
BEFORE ROW
, соответствующие типу события выполняемого действия.Выполняется указанное действие, при этом вызываются ограничения-проверки для целевой таблицы.
Вызываются все триггеры
AFTER ROW
, соответствующие типу события выполняемого действия.
Если целевое отношение является представлением с триггерами
INSTEAD OF ROW
, соответствующими типу события выполняемого действия, то триггеры выполняют эти действия.
Выполняются все триггеры
AFTER STATEMENT
для всех указанных действий, независимо от того, выполнялись ли эти действия фактически. Это похоже на поведение оператораUPDATE
, когда он не меняет ни одной строки.
То есть триггеры уровня оператора для некоторого события (скажем, 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.