MERGE

примечание

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

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

Синтаксис

[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
    USING data_source ON join_condition
    when_clause [...]

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 [ 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

Описание

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

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

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

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

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

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

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

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

Параметры

with_query

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

target_table_name

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

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_clause

Необходима хотя бы одно выражение WHEN.

Если предложение WHEN указывает WHEN MATCHED и строка источника совпадает со строкой в target_table_name, условие WHEN выполняется, и условие отсутствует или возвращает true.

Если предложение WHEN указывает WHEN NOT MATCHED и строка источника не совпадает со строкой в target_table_name, условие WHEN выполняется, если condition отсутствует или возвращает true.

condition

Выражение, которое возвращает значение типа boolean. Если это выражение для предложения WHEN возвращает true, то действие для этого предложения выполняется для этой строки.

Условие в предложении WHEN MATCHED может ссылаться на столбцы как в источнике, так и в целевых отношениях. Условие в предложении WHEN NOT MATCHED может ссылаться только на столбцы из отношения источника, поскольку по определению нет соответствующей целевой строки. Доступны только системные атрибуты таблицы назначения.

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, выражение может использовать значения из data_source.

DEFAULT

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

sub-SELECT

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

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

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

MERGE total_count

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

Примечания

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

1. Выполняются любые триггеры BEFORE STATEMENT для всех указанных действий, независимо от того, совпадают ли их условия WHEN.

2. Выполняется соединение из исходной таблицы в целевую таблицу. Результирующий запрос будет оптимизирован нормально и даст набор кандидатов для изменения строк. Для каждой строки кандидата изменений:

   2.1. Определяется, является ли строка MATCHED (нашлось совпадение) или NOT MATCHED (совпадения нет). 2.2. Условия WHEN проверяются по порядку, пока одно из них не вернет TRUE. 2.3. Как только условие WHEN возвращает TRUE, выполняются следующие действия:

   • Выполняются триггеры уровня строки (BEFORE ROW), срабатывающие на соответствующее событие (например, INSERT, UPDATE или DELETE).
   • Выполняется указанное действие, при этом проверяются ограничения (CHECK, NOT NULL, и так далее) целевой таблицы.
   • Выполняются триггеры уровня строки (AFTER ROW) для соответствующего типа события.

3. Выполнение любых AFTER STATEMENT триггеров для указанных действий, независимо от того, происходят они или нет. Это похоже на поведение оператора UPDATE, который не изменяет ни одной строки.

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

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

Если в предложении WHEN отсутствует дополнительное условие AND, оно становится последним достижимым предложением этого рода (MATCHED или NOT MATCHED). Если позже указывается предложение 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;

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

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

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

Предложение WITH и действие DO NOTHING являются расширениями стандарта SQL.