MERGE

примечание

Эта страница переведена при помощи нейросети GigaChat.

MERGE - условная вставка, обновление или удаление строк таблицы.

Синтаксис

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

Где data_source:

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

И when_clause:

{ 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 } }

И merge_insert:

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

И merge_update:

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

И merge_delete:

DELETE

Описание

MERGE выполняет действия, изменяющие строки в целевой таблице, указанной как target_table_name, используя данные из источника data_source. MERGE предоставляет единый SQL-оператор, который может условно выполнять вставку (INSERT), обновление (UPDATE) или удаление (DELETE) строк — то, что иначе потребовало бы нескольких операторов на процедурном языке.

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

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

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

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

Для MERGE не требуется отдельных привилегий. Привилегии зависят от выполняемых действий, при:

обновлении необходимо иметь привилегию UPDATE на соответствующие столбцы целевой таблицы, упомянутые в SET;
вставке — привилегию INSERT на целевую таблицу;
удалении — привилегию DELETE на целевую таблицу;
DO NOTHING — привилегию SELECT хотя бы на один столбец целевой таблицы.

Кроме того, требуется привилегия SELECT на все столбцы источника данных и целевой таблицы, которые участвуют в любых условиях (join_condition, WHEN, выражениях и так далее). Проверка привилегий осуществляется однократно — в начале выполнения команды, вне зависимости от того, какие WHEN выражения в итоге будут выполнены.

Команда MERGE не поддерживается, если целевая таблица является материализованным представлением, внешней таблицей или имеет определенные правила.

Параметры

with_query: Предложение WITH позволяет указать одно или несколько подзапросов, которые могут быть указаны по имени в запросе MERGE. Смотрите раздел SELECT для получения дополнительной информации.

target_table_name: Имя (необязательно квалифицированное именем схемы) целевой таблицы или представления, в которую необходимо объединить данные. Если перед именем таблицы указано ONLY, обновляются или удаляются только строки, соответствующие указанной таблице. Если ONLY не указан, также обновляются или удаляются совпадающие строки во всех таблицах, наследующих от указанной таблицы. Опционально после имени таблицы можно указать *, чтобы явно указать, что включаются дочерние таблицы. Ключевое слово ONLY и опция * не влияют на действия вставки, которые всегда вставляют данные только в указанную таблицу.

Если target_table_name является представлением, оно должно либо автоматически поддерживаться для обновления без каких-либо INSTEAD OF триггеров, либо иметь INSTEAD OF триггеры для каждого типа действий (INSERT, UPDATE и DELETE), указанных в предложениях WHEN. Представления с правилами не поддерживаются.

target_alias: Замещающее имя для целевой таблицы. Когда используется псевдоним, он полностью скрывает фактическое имя таблицы. Например, если дано MERGE INTO foo AS f, остальная часть предложения MERGE должна ссылаться на эту таблицу как f, а не foo.

source_table_name: Имя исходной таблицы, представления или переходной таблицы, при необходимости дополненное схемой. Если перед именем таблицы указано ONLY, совпадающие строки включаются только из указанной таблицы. Если ONLY не указан, совпадающие строки также включаются из любых таблиц, наследующих от указанной таблицы. Необязательно после имени таблицы можно указать *, чтобы явно указать, что включены дочерние таблицы.

source_query: Запрос (SELECT или VALUES), предоставляющий строки, которые должны быть объединены в target_table_name. Смотрите описание синтаксиса в SELECT или VALUES.

source_alias: Замещающее имя для источника данных. Когда предоставляется псевдоним, он полностью скрывает фактическое имя таблицы или тот факт, что был выполнен запрос.

join_condition: Выражение, результатом которого является значение типа boolean (аналогично предложению WHERE). Оно указывает, какие строки в data_source соответствуют строкам в target_table_name.
Предупреждение!
Только столбцы из target_table_name, которые пытаются соответствовать строкам data_source, должны появляться в join_condition. Подвыражения, содержащие только столбцы целевой таблицы, могут неожиданно повлиять на выбор действия.
Если указаны оба пункта WHEN NOT MATCHED BY SOURCE и WHEN NOT MATCHED [BY TARGET], команда MERGE выполнит соединение FULL между data_source и целевой таблицей. Чтобы это работало, хотя бы одно подвыражение join_condition должно использовать оператор, способный поддерживать хеш-соединение, или все подвыражения должны использовать операторы, способные поддерживать слияние соединений.

when_clause

Необходима хотя бы одно выражение 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 и строка-кандидат на изменение соответствует строке в data_source целевой таблице, выполняется пункт WHEN, если отсутствует condition или она оценивается как true.

Если в предложении WHEN указано WHEN NOT MATCHED BY SOURCE и строка-кандидат на изменение представляет строку в целевой таблице, которая не соответствует строке в data_source, выполняется пункт WHEN, если отсутствует condition или она оценивается как true.

Если в предложении WHEN указано WHEN NOT MATCHED [BY TARGET], а строка-кандидат на изменение представляет собой строку из таблицы data_source, которая не соответствует строке целевой таблицы, выполняется пункт WHEN, если элемент condition отсутствует или его значение равно true.

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

merge_insert

Описание действия UPDATE, обновляющего текущую строку целевой таблицы. Целевые имена столбцов могут быть перечислены в любом порядке. Если вообще не указан список имен столбцов, используется все столбцы таблицы в их объявленном порядке.

Каждый столбец, отсутствующий в явном или неявном списке столбцов, будет заполнен значением по умолчанию, либо его объявленным значением по умолчанию, либо нулевым, если оно отсутствует.

Если target_table_name является партиционированной таблицей, каждая строка направляется к соответствующей партиции и вставляется в нее. Если target_table_name является партицией, произойдет ошибка, если какая-либо входящая строка нарушает ограничение партиции.

Имена столбцов не могут быть указаны более одного раза. INSERT действия не могут содержать подзапросы.

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

merge_update: Описание действия UPDATE, которое обновляет текущую строку таблицы target_table_name. Имена столбцов могут быть указаны не более одного раза.
Ни имя таблицы, ни WHERE не допускаются.

merge_delete: Действие DELETE, которое удаляет текущую строку из target_table_name. Не включает в себя имя таблицы или любые другие предложения, как обычно делается с командой DELETE.

column_name: Имя столбца в target_table_name. Имя столбца может быть квалифицировано именем поля или индексом массива, если это необходимо. Вставка только в некоторые поля составного столбца оставляет остальные поля пустыми. Не включает имя таблицы в спецификацию целевого столбца.

OVERRIDING SYSTEM VALUE: Без этого предложения будет ошибкой явно указать значение (отличное от DEFAULT) для столбца идентичности, определенного как GENERATED ALWAYS. Это предложение отменяет это ограничение.

OVERRIDING USER VALUE: Если указано это предложение, то любые значения, указанные для столбцов идентичности, определенных как GENERATED BY DEFAULT, игнорируются и применяются значения последовательности по умолчанию.

DEFAULT VALUES: Все столбцы будут заполнены значениями по умолчанию. Предложение OVERRIDING не допускается в этой форме.

expression: Выражение, присваиваемое столбцу. Если используется в пункте WHEN MATCHED, выражение может использовать значения из исходной строки целевой таблицы и значения из строки data_source. Если используется в пункте WHEN NOT MATCHED BY SOURCE, выражение может использовать только значения из исходной строки целевой таблицы. Если используется в пункте WHEN NOT MATCHED [BY TARGET], выражение может использовать только значения из строки data_source.

DEFAULT: Установка значения по умолчанию для столбца (которое будет равно NULL, если ему не было присвоено конкретное выражение значения по умолчанию).

sub-SELECT: Подзапрос SELECT, который генерирует столько выходных столбцов, сколько указано в круглых скобках перед ним. Подзапрос должен выдавать не более одной строки при выполнении. Если он выдает одну строку, ее значения столбцов назначаются целевым столбцам. Если строк нет, целевым столбцам присваиваются значения NULL. Если подзапрос используется в предложении WHEN MATCHED, он может ссылаться на значения из исходной строки целевой таблицы и значения из строки data_source. Если подзапрос используется в предложении WHEN NOT MATCHED BY SOURCE, он может ссылаться только на значения из исходной строки целевой таблицы.

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

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

output_name: Имя, используемое для возвращаемого столбца.

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

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

MERGE total_count

где total_count - это общее количество измененных строк (вставленных, обновленных или удаленных). Если total_count равен 0, то строки не были изменены никоим образом.

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

Примечания

Следующие шаги происходят во время выполнения MERGE:

Выполняются любые триггеры BEFORE STATEMENT для всех указанных действий, независимо от того, совпадают ли их условия WHEN.
Выполняется соединение из исходной таблицы в целевую таблицу. Результирующий запрос будет оптимизирован нормально и даст набор кандидатов для изменения строк. Для каждой строки кандидата изменений:
1. Определяется, является ли строка MATCHED (совпадает), NOT MATCHED BY SOURCE (не совпадает по источнику) или NOT MATCHED [BY TARGET] (не совпадает по цели).
2. Условия WHEN проверяются по порядку, пока одно из них не вернет TRUE.
3. Как только условие WHEN оценивается как TRUE, выполняются следующие действия:
  - Выполняются триггеры уровня строки (BEFORE ROW), срабатывающие на соответствующее событие (например, INSERT, UPDATE или DELETE).
  - Выполняется указанное действие, при этом проверяются ограничения (CHECK, NOT NULL, и так далее) целевой таблицы.
  - Выполняются триггеры уровня строки (AFTER ROW) для соответствующего типа события.
Если целевое отношение является представлением с триггерами INSTEAD OF ROW для типа события данного действия, они используются вместо него для выполнения действия.
Выполнение любых AFTER STATEMENT триггеров для указанных действий, независимо от того, происходят они или нет. Это похоже на поведение оператора UPDATE, который не изменяет ни одной строки.

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

Убедитесь, что соединение производит не более одной строки изменения кандидата для каждой целевой строки. Другими словами, целевая строка не должна соединяться более чем с одной строкой источника данных. Если это произойдет, то будет использоваться только одна из строк изменения кандидатов для модификации целевой строки. Последующие попытки изменить строку вызовут ошибку. Это также может произойти, если триггеры строк вносят изменения в целевую таблицу, а затем строки, измененные таким образом, впоследствии также изменяются с помощью MERGE. Если повторяющееся действие является INSERT, это вызовет нарушение уникальности, тогда как повторение UPDATE или DELETE приведет к нарушению кардинальности (последнее поведение требуется стандартом SQL). Это отличается от исторического поведения PostgreSQL соединений в предложениях UPDATE и DELETE, где вторая и последующая попытка изменить одну и ту же строку просто игнорируются.

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

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

В операторе MERGE не допускается предложение RETURNING. INSERT, UPDATE и DELETE также не могут содержать предложения RETURNING или WITH.

Когда MERGE выполняется одновременно с другими командами, которые изменяют целевую таблицу, применяются обычные правила изоляции транзакций. Рассмотрите возможность использования 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.

Синтаксис​

Описание​

Параметры​

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

Примечания​

Примеры​

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