MERGE

PostgreSQL 15.4文書
		SQLコマンド
前へ	上へ	MERGE	次へ

MERGE — テーブルの行を条件付きでINSERT、UPDATE、DELETEする

概要

[ 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 [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE

説明

MERGEは、data_sourceを使用して、target_table_nameの行を変更するアクションを実行します。 MERGEは、条件付きでINSERT、UPDATEまたはDELETE行を使用できる単一のSQL文を提供します。この処理を行わないと、複数の手続き言語文が必要になります。

最初に、MERGEコマンドはdata_sourceからtarget_table_nameへの結合を実行し、0以上の候補変更行を生成します。各候補変更行に対して、MATCHEDまたはNOT MATCHEDのステータスが一度だけ設定され、その後WHEN句が指定された順序で評価されます。各候補変更行に対して、真と評価される最初の句が実行されます。どの候補変更行に対しても、1つのWHEN句しか実行されません。

MERGEアクションは、同じ名前の通常のUPDATE、INSERTまたはDELETEコマンドと同じ効果を持ちます。これらのコマンドの構文は異なり、WHERE句がなく、テーブル名が指定されていません。すべてのアクションはtarget_table_nameを参照しますが、他のテーブルへの変更はトリガを使用して行うことができます。

DO NOTHINGが指定されている場合、ソース行はスキップされます。アクションは指定された順序で評価されるため、DO NOTHINGは、より詳細な処理の前に、関心のないソース行をスキップする場合に便利です。

個別のMERGE権限はありません。更新アクションを指定する場合は、SET句で参照されるtarget_table_nameの列に対してUPDATE権限を持っている必要があります。挿入アクションを指定する場合は、target_table_nameに対してINSERT権限を持っている必要があります。削除アクションを指定する場合は、target_table_nameに対してDELETE権限を持っている必要があります。権限は文の開始時に一度テストされ、特定のWHEN句が実行されたかどうかがチェックされます。 data_sourceおよびconditionで参照されるtarget_table_nameのすべての列に対してSELECT権限が必要です。

target_table_nameが実体化ビュー(Materialized View)、外部テーブルである場合、またはテーブルに規則が定義されている場合、MERGEはサポートされません。

パラメータ

target_table_name

マージ先のターゲットテーブルの名前です(スキーマ修飾名も可)。テーブル名の前にONLYを指定すると、指定したテーブルでのみ一致する行が更新または削除されます。 ONLYを指定しないと、指定したテーブルを継承するテーブルでも一致する行が更新または削除されます。オプションで、テーブル名の後に*を指定して、子孫のテーブルが含まれることを明示的に示すことができます。 ONLYキーワードおよび*オプションは、挿入操作には影響しません。挿入操作では、常に指定したテーブルにのみ挿入します。

target_alias

ターゲットテーブルの代替名です。別名を指定すると、テーブルの実際の名前が完全に非表示になります。たとえば、MERGE INTO foo AS fを指定した場合、MERGE文の残りの部分は、このテーブルをfooではなくfとして参照する必要があります。

source_table_name

ソーステーブル、ビュー、または遷移テーブルの名前(スキーマ修飾名も可)。テーブル名の前にONLYを指定すると、指定したテーブルのみからの一致する行が含まれます。 ONLYを指定しないと、指定したテーブルを継承するすべてのテーブルからも一致する行が含まれます。オプションで、テーブル名の後に*を指定して、子孫のテーブルが含まれることを明示的に示すことができます。

source_query

target_table_nameにマージされる行を提供する問合せ（SELECT文またはVALUES文）です。構文の説明は、SELECT文またはVALUES文を参照してください。

source_alias

データソースの代替名です。別名を指定すると、テーブルの実際の名前やクエリが発行された事実が完全に非表示になります。

join_condition

join_conditionはboolean型の値を返す式です（WHERE句に似ています）。この式は、data_sourceのどの行がtarget_table_nameの行と一致するかを指定します。

警告

join_conditionには、data_source行に一致しようとするtarget_table_nameの列のみが表示されます。 target_table_name列のみを参照するjoin_conditionサブ式は、実行されるアクションに影響を与える可能性があり、多くの場合驚くべき方法で影響を与えます。

when_clause

少なくとも1つのWHEN句が必要です。

WHEN句でWHEN MATCHEDが指定され、変更候補行がtarget_table_nameの行と一致する場合、conditionが存在しないか真と評価されるとWHEN句が実行されます。

逆に、WHEN句がWHEN NOT MATCHEDを指定し、変更候補行がtarget_table_nameの行と一致しない場合、conditionが存在しないか、真と評価されたときにWHEN句が実行されます。

condition

boolean型の値を返す式。 WHEN句のこの式が真を返す場合、その句のアクションがその行に対して実行されます。

WHEN MATCHED句の条件は、ソースリレーションとターゲットリレーションの両方の列を参照できます。 WHEN NOT MATCHED句の条件は、ソースリレーションの列のみを参照できます。これは、定義上、一致するターゲット行がないためです。ターゲットテーブルのシステム属性のみにアクセスできます。

merge_insert

ターゲットテーブルに1つの行を挿入するINSERTアクションの指定。ターゲット列名は任意の順序でリストできます。列名のリストがまったく指定されていない場合、デフォルトではテーブルのすべての列が宣言された順序になります。

明示的または暗黙的な列リストにない各列にはデフォルト値（デフォルト値が宣言されていればその値、未宣言ならばNULL）が挿入されます。

target_table_nameがパーティションテーブルの場合、各行は適切なパーティションにルーティングされて挿入されます。 target_table_nameがパーティションの場合、入力行がパーティション制約に違反するとエラーが発生します。

列名を複数回指定することはできません。 INSERTアクションに副選択を含めることはできません。

VALUES句は1つしか指定できません。 VALUES句はソースリレーションの列のみを参照できます。これは、定義上、一致するターゲット行がないためです。

merge_update

target_table_nameの現在の行を更新するUPDATEアクションの指定。列名は2回以上指定できません。

テーブル名もWHERE句も使用できません。

merge_delete

target_table_nameの現在の行を削除するDELETEアクションを指定します。 DELETEコマンドで通常行うように、テーブル名やその他の句は含めないでください。

column_name

target_table_name内の列名。列名は、必要に応じてサブフィールド名または配列の添字で修飾できます。（複合列の一部のフィールドにのみ挿入すると、他のフィールドはNULLになります。）ターゲット列の指定には、テーブルの名前を含めないでください。

OVERRIDING SYSTEM VALUE

この句を使用しない場合、GENERATED ALWAYSとして定義されたID列に対して明示的な値(DEFAULT以外)を指定するとエラーになります。この句は、この制限を上書きします。

OVERRIDING USER VALUE

この句を指定した場合、GENERATED BY DEFAULTとして定義されたID列に提供された値は無視され、シーケンスで生成されたデフォルト値が適用されます。

DEFAULT VALUES

すべての列にデフォルト値が設定されます(このフォームではOVERRIDING句は使用できません)。

expression

列に割り当てる式。 WHEN MATCHED句で使用する場合、式ではターゲットテーブルの元の行の値とdata_source行の値を使用できます。 WHEN NOT MATCHED句で使用する場合、式ではdata_sourceの値を使用できます。

DEFAULT

列をデフォルト値に設定します（特定のデフォルト式が割り当てられていない場合はNULLになります）。

with_query

WITH句を使用すると、MERGEクエリで名前で参照できる1つ以上のサブクエリを指定できます。詳細は7.8とSELECTを参照してください。

出力

正常に完了すると、MERGEコマンドは以下の形式のコマンドタグを返します。

MERGE total_count

total_countは変更された行の合計数です(挿入、更新、または削除のいずれか)。 total_countが0の場合、行はまったく変更されていません。

注釈

次のステップは、MERGEの実行中に行われます。

WHEN句が一致するかどうかに関係なく、指定されたすべてのアクションに対してBEFORE STATEMENTトリガを実行します。
ソーステーブルからターゲットテーブルへの結合を実行します。結果の問合せは通常どおり最適化され、一連の候補変更行が生成されます。候補変更行ごとに、
1. 各行がMATCHEDまたはNOT MATCHEDであるかどうかを評価します。
2. 真が返されるまで、各WHEN条件を指定された順序でテストします。
3. 条件が真を返す場合は、次のアクションを実行します。
  1. アクションのイベントタイプに対して起動するBEFORE ROWトリガを実行します。
  2. 指定されたアクションを実行し、ターゲットテーブルの検査制約を呼び出します。
  3. アクションのイベントタイプに対して起動するAFTER ROWトリガを実行します。
アクションが実際に発生するかどうかに関係なく、指定されたアクションに対してAFTER STATEMENTトリガを実行します。これは、行を変更しないUPDATE文の動作に似ています。

要約するとイベントタイプの文トリガ（たとえば、INSERTなど）は、その種類のアクションを指定するたびに起動されます。対照的に、行レベルトリガは、実行される特定のイベントタイプに対してのみ起動されます。したがって、MERGEコマンドでは、UPDATE行トリガのみが起動された場合でも、UPDATEとINSERTの両方に対して文トリガを起動する可能性があります。

結合では、各ターゲット行に対して最大1つの候補変更行が生成されるようにする必要があります。つまり、ターゲット行は複数のデータソース行に結合できません。結合する場合、候補変更行の1つだけがターゲット行の変更に使用されます。後で行を変更しようとするとエラーが発生します。これは、行トリガがターゲットテーブルを変更し、変更された行が後でMERGEによっても変更される場合にも発生する可能性があります。繰り返されるアクションがINSERTの場合、一意性違反が発生しますが、UPDATEまたはDELETEを繰り返すとカーディナリティ違反が発生します。後者の動作はSQL標準で要求されています。これは、PostgreSQLのUPDATEおよびDELETE文における結合の歴史的な動作とは異なります。この動作では、2回目以降の同じ行の変更は単純に無視されます。

WHEN句でAND副句が省略された場合、その句はその種類の最終到達可能句(MATCHEDまたはNOT MATCHED)になります。その種類の後のWHEN句が指定された場合、到達不能である可能性があり、エラーが発生します。いずれの種類の最終到達可能句も指定されていない場合、候補変更行に対してアクションが実行されない可能性があります。

デフォルトでは、データソースから行が生成される順序は不定です。 source_queryを使用して、必要に応じて一貫した順序を指定できます。これは、コンカレント・トランザクション間のデッドロックを回避するために必要になる場合があります。

MERGEにはRETURNING句はありません。 INSERT、UPDATE、DELETEのアクションにRETURNING句やWITH句を含めることはできません。

MERGEをターゲットテーブルを変更する他のコマンドと同時に実行すると、通常のトランザクション分離ルールが適用されます。各分離レベルでの動作の説明は13.2を参照してください。また、INSERT ... ON CONFLICTを代替文として使用することも検討できます。この文は、同時INSERTが発生した場合にUPDATEを実行する機能を提供します。 2つの文タイプの間には様々な違いや制限があり、相互に交換することはできません。

例

新規recent_transactionsに基づいて、customer_accountsのメンテナンスを実行します。

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);

在庫数量とともに新規在庫品目を挿入しようとしました。品目がすでに存在する場合は、既存品目の在庫数を更新します。在庫数が0のエントリは許可しません。

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標準の拡張です。