MERGE

PostgreSQL 16.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 [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
             ( column_name [, ...] ) = ( sub-SELECT )
           } [, ...]

and merge_delete is:

DELETE

説明

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

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

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

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

別個のMERGE権限はありません。更新アクションを指定する場合、SET句で参照される対象テーブルの列に対するUPDATE権限が必要です。挿入アクションを指定する場合、対象テーブルに対するINSERT権限が必要です。削除アクションを指定する場合、対象テーブルに対するDELETE権限が必要です。 DO NOTHINGアクションを指定する場合、対象テーブルの少なくとも1つの列に対するSELECT権限が必要です。また、data_sourceの任意の列とcondition（join_conditionを含む）またはexpressionで参照される対象テーブルの任意の列に対するSELECT権限も必要です。権限は文の開始時に一度テストされ、特定のWHEN句が実行されるかどうかがチェックされます。

対象テーブルがマテリアライズドビュー、外部テーブルである場合、またはテーブルにルールが定義されている場合、MERGEはサポートされません。

パラメータ

with_query

WITH句を使用すると、MERGE問い合わせで名前で参照できる1つ以上の副問い合わせを指定できます。詳細は7.8とSELECTを参照してください。 WITH RECURSIVEはMERGEではサポートされていないことに注意してください。

target_table_name

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

target_alias

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

source_table_name

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

source_query

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

source_alias

データソースの代替名です。別名を指定すると、テーブルの実際の名前や問い合わせが発行された事実が完全に隠されます。

join_condition

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

警告

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

when_clause

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

WHEN句でWHEN MATCHEDが指定され、変更候補行が対象テーブルの行と一致する場合、conditionが存在しないかtrueと評価されるとWHEN句が実行されます。

逆に、WHEN句がWHEN NOT MATCHEDを指定し、変更候補行が対象テーブルの行と一致しない場合、conditionが存在しないかtrueと評価されるとWHEN句が実行されます。

condition

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

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

merge_insert

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

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

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

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

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

merge_update

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

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

merge_delete

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

column_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になります）。

sub-SELECT

それに先行する括弧付きの列リストに列挙されている出力列と同じ数の出力列を生成するSELECT副問い合わせ。副問い合わせは、実行時に2行以上生成してはいけません。行が1つ返されると、その列の値が対象列に割り当てられます。行が返されない場合は、NULL値が対象列に割り当てられます。副問い合わせは、対象テーブルの元の行からの値とdata_source行からの値を参照できます。

出力

正常に完了すると、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の拡張です。