MERGE

PostgreSQL 17.0文書
		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 [...]
[ RETURNING { * | 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

説明

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

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

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

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

オプションのRETURNING句は、挿入、更新、削除された各行に基づいて値を計算し、返すようにMERGEを設定します。元となるテーブルまたは対象テーブルの列、あるいはmerge_action()関数を使用する式が計算可能です。 INSERTまたはUPDATEアクションが実行されると、対象テーブルの列の新しい値が使用されます。 DELETEが実行されると、対象テーブルの列の古い値が使用されます。 RETURNINGリストの構文は、SELECTの出力リストと同じです。

別個の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_table_nameがビューの場合、INSTEAD OFトリガを使用せずに自動的に更新可能であるか、WHEN句で指定されたすべてのアクション（INSERT、UPDATE、DELETE）に対してINSTEAD OFトリガを持つ必要があります。ルール付きビューはサポートされていません。

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 NOT MATCHED BY SOURCEとWHEN NOT MATCHED [BY TARGET]の両方の句が指定された場合、MERGEコマンドはdata_sourceと対象テーブルの間でFULL結合を実行します。これを機能させるには、少なくとも1つのjoin_condition副式がハッシュ結合をサポートできる演算子を使用するか、すべての副式がマージ結合をサポートできる演算子を使用する必要があります。

when_clause

少なくとも1つの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の拡張です。その意味をより明確にする、WHEN NOT MATCHEDにBY TARGETを付加するオプションも同様に拡張です。

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

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

WHEN句でWHEN NOT MATCHED [BY TARGET]が指定され、候補変更行がdata_sourceの行を表し、その行が対象テーブルの行と一致しない場合、conditionが存在しないかtrueと評価された場合にWHEN句が実行されます。

condition

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

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

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 BY SOURCE句で使用する場合、式は対象テーブルの元の行からの値のみを使用できます。 WHEN NOT MATCHED [BY TARGET]句で使用する場合、式はdata_source行からの値のみを使用できます。

DEFAULT

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

sub-SELECT

それに先行する括弧付きの列リストに列挙されている出力列と同じ数の出力列を生成するSELECT副問い合わせ。副問い合わせは、実行時に2行以上生成してはいけません。行が1つ返されると、その列の値が対象列に割り当てられます。行が返されない場合は、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句を含む場合、結果はRETURNINGリストに定義された列と値を含むSELECT文と同様になります。この場合、コマンドにより挿入、更新、削除された行に対して計算されます。

注釈

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

WHEN句が一致するかどうかに関係なく、指定されたすべてのアクションに対してBEFORE STATEMENTトリガを実行します。
元となるテーブルから対象テーブルへの結合を実行します。結果の問合せは通常どおり最適化され、一連の変更候補行が生成されます。各変更候補行について、
1. 各行がMATCHED、NOT MATCHED BY SOURCE、またはNOT MATCHED [BY TARGET]のいずれであるかを評価します。
2. 真が返されるまで、各WHEN条件を指定された順序でテストします。
3. 条件が真を返す場合は、次のアクションを実行します。
  1. アクションのイベントタイプに対して起動するBEFORE ROWトリガを実行します。
  2. 指定されたアクションを実行し、対象テーブルの検査制約を呼び出します。
  3. アクションのイベントタイプに対して起動するAFTER ROWトリガを実行します。
  対象リレーションがアクションのイベントタイプに対してINSTEAD OF 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 BY SOURCEまたはNOT MATCHED [BY TARGET])になります。その種類の後のWHEN句が指定された場合、到達不能である可能性があり、エラーが発生します。いずれの種類の最終到達可能句も指定されていない場合、候補変更行に対してアクションが実行されない可能性があります。

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

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
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句、WHEN NOT MATCHEDのBY SOURCEおよびBY TARGET修飾子、DO NOTHINGアクションおよびRETURNING句は、標準SQLの拡張です。