Postgres Pro Shardman : Документация: 18: MERGE : Компания Postgres Professional

MERGE
Пред.	Наверх	Команды SQL	Начало	След.

MERGE

MERGE — добавить, изменить или удалить строки таблицы по условию

Синтаксис

[ WITH запрос_WITH [, ...] ]
MERGE INTO [ ONLY ] имя_целевой_таблицы [ * ] [ [ AS ] целевой_псевдоним ]
    USING источник_данных ON условие_соединения
    предложение_when [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS псевдоним_результата [, ...] ) ]
                { * | выражение_результата [ [ 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 подзапрос может обращаться только к значениям исходной строки в целевой таблице.

псевдоним_результата

Необязательный псевдоним для строк OLD или NEW в списке RETURNING.

По умолчанию старые значения из целевой таблицы можно получить с помощью OLD.имя_столбца или OLD.*, а новые — с помощью NEW.имя_столбца или NEW.*. Если заданы псевдонимы, эти имена недоступны, и обращаться к значениям нужно через псевдонимы, например RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.

выражение_результата

Выражение, вычисляемое и возвращаемое командой MERGE после изменения каждой строки (добавления, изменения или удаления). В этом выражении можно использовать имена любых столбцов исходной и целевой таблицы или функцию merge_action() для получения дополнительной информации о выполняемых действиях.

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

Чтобы получить старые или новые значения из целевой таблицы, можно дополнить имя столбца или * с помощью OLD или NEW или соответствующего псевдонима_результата для OLD или NEW. При использовании имени столбца без дополнений или же имени столбца или *, дополненных именем или псевдонимом целевой таблицы, возвращаются новые значения для действий INSERT и UPDATE и старые значения для действий DELETE.

имя_результата

Имя, назначаемое возвращаемому столбцу.

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

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

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

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

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

Примечания

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

Вызываются все триггеры BEFORE STATEMENT для всех указанных действий, независимо от того, совпадают ли их предложения WHEN.
Выполняется соединение исходной таблицы с целевой. Полученный в результате запрос оптимизируется как обычно и выдаёт набор строк-кандидатов на изменение. Для каждой строки-кандидата на изменение:
1. Для каждой строки определяется состояние: MATCHED (совпадает), NOT MATCHED BY SOURCE (не совпадает по источнику) или NOT MATCHED [BY TARGET] (не совпадает по цели).
2. Проверяется каждое условие WHEN в заданном порядке, пока какое-либо не выдаст значение true.
3. Если условие оценивается как true, происходит следующее:
  1. Вызываются все триггеры BEFORE ROW, соответствующие типу события выполняемого действия.
  2. Выполняется указанное действие, при этом вызываются ограничения-проверки для целевой таблицы.
  3. Вызываются все триггеры 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);

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

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.winename, old.stock AS old_stock, new.stock AS new_stock;

Таблица 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.

Пред.	Наверх	След.
LOCK	Начало	MOVE

MERGE
Prev	Up	SQL Commands	Home	Next

MERGE

MERGE — conditionally insert, update, or delete rows of a table

Synopsis

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
    USING data_source ON join_condition
    when_clause [...]
    [ RETURNING [ WITH ( { OLD | NEW } AS output_alias [, ...] ) ]
                { * | output_expression [ [ AS ] output_name ] } [, ...] ]

where data_source is:

    { [ ONLY ] source_table_name [ * ] | ( source_query ) } [ [ AS ] source_alias ]

and when_clause is:

    { WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
      WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
      WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }

and merge_insert is:

    INSERT [( column_name [, ...] )]
        [ OVERRIDING { SYSTEM | USER } VALUE ]
        { VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

    UPDATE SET { column_name = { expression | DEFAULT } |
                 ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
                 ( column_name [, ...] ) = ( sub-SELECT )
               } [, ...]

and merge_delete is:

    DELETE

Description

MERGE performs actions that modify rows in the target table identified as target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements.

First, the MERGE command performs a join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET] is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed for any candidate change row.

MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names. The syntax of those commands is different, notably that there is no WHERE clause and no table name is specified. All actions refer to the target table, though modifications to other tables may be made using triggers.

When DO NOTHING is specified, the source row is skipped. Since actions are evaluated in their specified order, DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling.

The optional RETURNING clause causes MERGE to compute and return value(s) based on each row inserted, updated, or deleted. Any expression using the source or target table's columns, or the merge_action() function can be computed. By default, when an INSERT or UPDATE action is performed, the new values of the target table's columns are used, and when a DELETE is performed, the old values of the target table's columns are used, but it is also possible to explicitly request old and new values. The syntax of the RETURNING list is identical to that of the output list of SELECT.

There is no separate MERGE privilege. If you specify an update action, you must have the UPDATE privilege on the column(s) of the target table that are referred to in the SET clause. If you specify an insert action, you must have the INSERT privilege on the target table. If you specify a delete action, you must have the DELETE privilege on the target table. If you specify a DO NOTHING action, you must have the SELECT privilege on at least one column of the target table. You will also need SELECT privilege on any column(s) of the data_source and of the target table referred to in any condition (including join_condition) or expression. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are executed.

MERGE is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it.

Parameters

with_query

The WITH clause allows you to specify one or more subqueries that can be referenced by name in the MERGE query. See Section 7.8 and SELECT for details. Note that WITH RECURSIVE is not supported by MERGE.

target_table_name

The name (optionally schema-qualified) of the target table or view to merge into. If ONLY is specified before a table name, matching rows are updated or deleted in the named table only. If ONLY is not specified, matching rows are also updated or deleted in any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included. The ONLY keyword and * option do not affect insert actions, which always insert into the named table only.

If target_table_name is a view, it must either be automatically updatable with no INSTEAD OF triggers, or it must have INSTEAD OF triggers for every type of action (INSERT, UPDATE, and DELETE) specified in the WHEN clauses. Views with rules are not supported.

target_alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given MERGE INTO foo AS f, the remainder of the MERGE statement must refer to this table as f not foo.

source_table_name

The name (optionally schema-qualified) of the source table, view, or transition table. If ONLY is specified before the table name, matching rows are included from the named table only. If ONLY is not specified, matching rows are also included from any tables inheriting from the named table. Optionally, * can be specified after the table name to explicitly indicate that descendant tables are included.

source_query

A query (SELECT statement or VALUES statement) that supplies the rows to be merged into the target table. Refer to the SELECT statement or VALUES statement for a description of the syntax.

source_alias

A substitute name for the data source. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued.

join_condition

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the data_source match rows in the target table.

Warning

Only columns from the target table that attempt to match data_source rows should appear in join_condition. join_condition subexpressions that only reference the target table's columns can affect which action is taken, often in surprising ways.

If both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY TARGET] clauses are specified, the MERGE command will perform a FULL join between data_source and the target table. For this to work, at least one join_condition subexpression must use an operator that can support a hash join, or all of the subexpressions must use operators that can support a merge join.

when_clause

At least one WHEN clause is required.

The WHEN clause may specify WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT MATCHED [BY TARGET]. Note that the SQL standard only defines WHEN MATCHED and WHEN NOT MATCHED (which is defined to mean no matching target row). WHEN NOT MATCHED BY SOURCE is an extension to the SQL standard, as is the option to append BY TARGET to WHEN NOT MATCHED, to make its meaning more explicit.

If the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in the data_source to a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

If the WHEN clause specifies WHEN NOT MATCHED BY SOURCE and the candidate change row represents a row in the target table that does not match a row in the data_source, the WHEN clause is executed if the condition is absent or it evaluates to true.

If the WHEN clause specifies WHEN NOT MATCHED [BY TARGET] and the candidate change row represents a row in the data_source that does not match a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

condition

An expression that returns a value of type boolean. If this expression for a WHEN clause returns true, then the action for that clause is executed for that row.

A condition on a WHEN MATCHED clause can refer to columns in both the source and the target relations. A condition on a WHEN NOT MATCHED BY SOURCE clause can only refer to columns from the target relation, since by definition there is no matching source row. A condition on a WHEN NOT MATCHED [BY TARGET] clause can only refer to columns from the source relation, since by definition there is no matching target row. Only the system attributes from the target table are accessible.

merge_insert

The specification of an INSERT action that inserts one row into the target table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the target table is a partition, an error will occur if any input row violates the partition constraint.

Column names may not be specified more than once. INSERT actions cannot contain sub-selects.

Only one VALUES clause can be specified. The VALUES clause can only refer to columns from the source relation, since by definition there is no matching target row.

merge_update

The specification of an UPDATE action that updates the current row of the target table. Column names may not be specified more than once.

Neither a table name nor a WHERE clause are allowed.

merge_delete

Specifies a DELETE action that deletes the current row of the target table. Do not include the table name or any other clauses, as you would normally do with a DELETE command.

column_name

The name of a column in the target table. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) Do not include the table's name in the specification of a target column.

OVERRIDING SYSTEM VALUE

Without this clause, it is an error to specify an explicit value (other than DEFAULT) for an identity column defined as GENERATED ALWAYS. This clause overrides that restriction.

OVERRIDING USER VALUE

If this clause is specified, then any values supplied for identity columns defined as GENERATED BY DEFAULT are ignored and the default sequence-generated values are applied.

DEFAULT VALUES

All columns will be filled with their default values. (An OVERRIDING clause is not permitted in this form.)

expression

An expression to assign to the column. If used in a WHEN MATCHED clause, the expression can use values from the original row in the target table, and values from the data_source row. If used in a WHEN NOT MATCHED BY SOURCE clause, the expression can only use values from the original row in the target table. If used in a WHEN NOT MATCHED [BY TARGET] clause, the expression can only use values from the data_source row.

DEFAULT

Set the column to its default value (which will be NULL if no specific default expression has been assigned to it).

sub-SELECT

A SELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. If used in a WHEN MATCHED clause, the sub-query can refer to values from the original row in the target table, and values from the data_source row. If used in a WHEN NOT MATCHED BY SOURCE clause, the sub-query can only refer to values from the original row in the target table.

output_alias

An optional substitute name for OLD or NEW rows in the RETURNING list.

By default, old values from the target table can be returned by writing OLD.column_name or OLD.*, and new values can be returned by writing NEW.column_name or NEW.*. When an alias is provided, these names are hidden and the old or new rows must be referred to using the alias. For example RETURNING WITH (OLD AS o, NEW AS n) o.*, n.*.

output_expression

An expression to be computed and returned by the MERGE command after each row is changed (whether inserted, updated, or deleted). The expression can use any columns of the source or target tables, or the merge_action() function to return additional information about the action executed.

Writing * will return all columns from the source table, followed by all columns from the target table. Often this will lead to a lot of duplication, since it is common for the source and target tables to have a lot of the same columns. This can be avoided by qualifying the * with the name or alias of the source or target table.

A column name or * may also be qualified using OLD or NEW, or the corresponding output_alias for OLD or NEW, to cause old or new values from the target table to be returned. An unqualified column name from the target table, or a column name or * qualified using the target table name or alias will return new values for INSERT and UPDATE actions, and old values for DELETE actions.

output_name

A name to use for a returned column.

Outputs

On successful completion, a MERGE command returns a command tag of the form

MERGE total_count

The total_count is the total number of rows changed (whether inserted, updated, or deleted). If total_count is 0, no rows were changed in any way.

If the MERGE command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the RETURNING list, computed over the row(s) inserted, updated, or deleted by the command.

Notes

The following steps take place during the execution of MERGE.

Perform any BEFORE STATEMENT triggers for all actions specified, whether or not their WHEN clauses match.
Perform a join from source to target table. The resulting query will be optimized normally and will produce a set of candidate change rows. For each candidate change row,
1. Evaluate whether each row is MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET].
2. Test each WHEN condition in the order specified until one returns true.
3. When a condition returns true, perform the following actions:
  1. Perform any BEFORE ROW triggers that fire for the action's event type.
  2. Perform the specified action, invoking any check constraints on the target table.
  3. Perform any AFTER ROW triggers that fire for the action's event type.
  If the target relation is a view with INSTEAD OF ROW triggers for the action's event type, they are used to perform the action instead.
Perform any AFTER STATEMENT triggers for actions specified, whether or not they actually occur. This is similar to the behavior of an UPDATE statement that modifies no rows.

In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. In contrast, row-level triggers will fire only for the specific event type being executed. So a MERGE command might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.

You should ensure that the join produces at most one candidate change row for each target row. In other words, a target row shouldn't join to more than one data source row. If it does, then only one of the candidate change rows will be used to modify the target row; later attempts to modify the row will cause an error. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by MERGE. If the repeated action is an INSERT, this will cause a uniqueness violation, while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL standard. This differs from historical PostgreSQL behavior of joins in UPDATE and DELETE statements where second and subsequent attempts to modify the same row are simply ignored.

If a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind (MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET]). If a later WHEN clause of that kind is specified it would be provably unreachable and an error is raised. If no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row.

The order in which rows are generated from the data source is indeterminate by default. A source_query can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions.

When MERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see Section 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider using INSERT ... ON CONFLICT as an alternative statement which offers the ability to run an UPDATE if a concurrent INSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

Examples

Perform maintenance on customer_accounts based upon new 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);

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don't allow entries that have zero stock. Return details of all changes made.

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.winename, old.stock AS old_stock, new.stock AS new_stock;

The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.

Update wines based on a replacement wine list, inserting rows for any new stock, updating modified stock entries, and deleting any wines not present in the new list.

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;

Compatibility

This command conforms to the SQL standard.

The WITH clause, BY SOURCE and BY TARGET qualifiers to WHEN NOT MATCHED, DO NOTHING action, and RETURNING clause are extensions to the SQL standard.

Prev	Up	Next
LOCK	Home	MOVE