# Postgres の INSERT ... ON CONFLICT と MERGE：どちらで upsert すべきか、それぞれが出すエラー PostgreSQL 17 で INSERT ... ON CONFLICT と MERGE をどう使い分けるか、MERGE が並行実行時に重複キーエラーで失敗する理由、そして「cannot affect row a second time」、空の RETURNING、「no unique or exclusion constraint matching the ON CONFLICT specification」の直し方を説明します。すべてのステートメントを Postgres 17.11 で再現しています。 **Postgres には「この行を挿入する。すでにあれば更新する」と書く方法が 2 つあります。9.5 からある `INSERT ... ON CONFLICT` と、15 で追加され、17 で `RETURNING`、`merge_action()`、`WHEN NOT MATCHED BY SOURCE` が加わってぐっと使いやすくなった `MERGE` です。どちらでも同じように見えますが、そうではありません。2 つのセッションが同じ新しいキーを upsert すると、`ON CONFLICT` では両方とも成功しますが、`MERGE` では片方が重複キーエラーになります。この記事では、この競合と、upsert でよく遭遇するほかの 5 つのエラーや意外な挙動を再現し、それぞれの修正方法を示し、最後にどちらを選ぶかの基準を示します。以下のステートメントと出力はすべて、Relyt One が採用しているメジャーバージョンであるローカルの PostgreSQL 17.11 で実行したものです。** ## 要点 同時に実行される書き込みが同じキーを挿入する可能性があるとき（リクエストハンドラー、Webhook のコンシューマー、カウンターなど）は `INSERT ... ON CONFLICT` を使います。[Postgres のドキュメント](https://www.postgresql.org/docs/17/sql-insert.html)は「アトミックな `INSERT` または `UPDATE` の結果。独立したエラーがない限り、高い並行性のもとでも、2 つの結果のどちらか一方が保証される」と保証しています。夜間のフィードやステージングテーブルのように行の集合全体をテーブルと突き合わせるときで、条件付きの更新や削除、各行に何が起きたかのレポートが必要な場合は `MERGE` を使います。 | 遭遇するもの | ステートメント | 理由 | 修正方法 | | ----------------------------------------------------------------------------------- | ------------------------ | ---------------------------------------------------- | -------------------------------------------------- | | `duplicate key value violates unique constraint` | `MERGE` | 2 つのセッションが同じ新しいキーを挿入し、`MERGE` は `UPDATE` にフォールバックしない | その経路では `ON CONFLICT` を使うか、SQLSTATE `23505` でリトライする | | `ON CONFLICT DO UPDATE command cannot affect row a second time` | `INSERT ... ON CONFLICT` | バッチに同じキーが 2 回含まれている | `DISTINCT ON` でバッチの重複を除く | | `MERGE command cannot affect row a second time` | `MERGE` | 2 つのソース行が 1 つのターゲット行にマッチする | ソースの重複を除く | | `RETURNING` が空で返る | 両方 | 実際に挿入または更新された行しか返らない | 同じステートメント内で既存の行を読む | | `there is no unique or exclusion constraint matching the ON CONFLICT specification` | `INSERT ... ON CONFLICT` | 競合ターゲットがどのユニークインデックスにも一致しない | インデックスを追加するか、部分インデックスの `WHERE` を繰り返す | | 何も変わっていないのに行が書き換えられる | 両方 | 値が同じでも、更新は新しい行バージョンを書き込む | `WHERE ... IS DISTINCT FROM` を追加する | | ID に欠番が生じる | `INSERT ... ON CONFLICT` | 競合を検出する前に ID が採番される | 欠番を前提にし、ID を件数として使わない | ## 準備 Postgres 17 ならどれでも動きます。Docker の場合は次のとおりです。 ```bash docker run -d --name upsert-lab -e POSTGRES_PASSWORD=secret postgres:17.11-alpine docker exec -it upsert-lab psql -U postgres ``` ID 列をキーとし、`sku` をユニークにしたテーブルを 1 つ作ります。 ```sql CREATE TABLE products ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, sku text NOT NULL UNIQUE, name text NOT NULL, price numeric(10,2) NOT NULL ); ``` 基本の upsert では競合する列を指定します。`EXCLUDED` は挿入しようとした行です。 ```sql INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 49.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price RETURNING id, sku, price, (xmax = 0) AS inserted; ``` ```text id | sku | price | inserted ----+-------+-------+---------- 1 | KB-01 | 49.00 | t ``` `45.00` でもう一度実行すると、同じ行が更新されて返り、`inserted` は false になります。 ```text id | sku | price | inserted ----+-------+-------+---------- 1 | KB-01 | 45.00 | f ``` `(xmax = 0)` の判定は、挿入と更新を見分けるために広く使い回されているテクニックです。挿入されたばかりの行バージョンには `xmax` がないので機能しますが、ドキュメント化された挙動ではなく、Postgres が行バージョンを格納する仕組みに依存しています。`MERGE` にはアクションを報告するドキュメント化された方法があり、この記事の最後で紹介します。 ## 1. 2 つのセッションと 1 つの新しいキー：`MERGE` は失敗し、`ON CONFLICT` は失敗しない `psql` のセッションを 2 つ開きます。1 つ目でトランザクションを開始し、まだ存在しない SKU を `MERGE` で upsert しますが、コミットはしません。 ```sql -- セッション A BEGIN; MERGE INTO products AS p USING (VALUES ('WC-01', 'Webcam', 59.00)) AS f(sku, name, price) ON p.sku = f.sku WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price); ``` 2 つ目のセッションで、同じ SKU に対して同じ `MERGE` を実行します。処理が止まります。 ```sql -- セッション B MERGE INTO products AS p USING (VALUES ('WC-01', 'Webcam', 55.00)) AS f(sku, name, price) ON p.sku = f.sku WHEN MATCHED THEN UPDATE SET name = f.name, price = f.price WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price); ``` セッション A で `COMMIT;` と入力します。セッション B は次のエラーで再開します。 ```text ERROR: duplicate key value violates unique constraint "products_sku_key" DETAIL: Key (sku)=(WC-01) already exists. ``` ![2 本のタイムライン。MERGE では、セッション B は行を見つけられずに挿入しようとしてセッション A を待ち、A がコミットすると重複キーエラーで失敗する。INSERT ... ON CONFLICT では、セッション B は同じように待ったあと、A が挿入した行を更新する。](/blog/assets/1810455f7a4b4d55.svg) 何が起きたのかを説明します。セッション B が開始した時点では A の行はコミットされていなかったので、B の結合はマッチを見つけられず、B は `WHEN NOT MATCHED` 分岐を選びました。その後 B の挿入は、ユニークインデックス上で A の終了を待ちました。A がコミットした時点でキーは存在していますが、`MERGE` にはその時点で `WHEN MATCHED` 分岐に切り替える手段がありません。[`MERGE` のドキュメント](https://www.postgresql.org/docs/17/sql-merge.html)もそう述べており、「並行する `INSERT` が発生した場合に `UPDATE` を実行できる代替のステートメント」として `INSERT ... ON CONFLICT` を案内しています。 次に、別の新しい SKU を使って、同じ競合を `ON CONFLICT` で試します。 ```sql -- セッション A BEGIN; INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 79.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price; ``` ```sql -- セッション B：A を待つ INSERT INTO products (sku, name, price) VALUES ('HS-01', 'Headset', 75.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price; ``` セッション A で `COMMIT;` を実行すると、セッション B は `INSERT 0 1` を表示し、行には B の価格が入ります。 ```sql SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku; ``` ```text sku | price -------+------- HS-01 | 75.00 WC-01 | 59.00 ``` `WC-01` にセッション A の価格が残っているのは、セッション B の `MERGE` が失敗したからです。2 つの書き込みが同じキーを作成しうる経路で `MERGE` を使う場合は、SQLSTATE `23505` を捕捉してトランザクション全体をリトライしてください。デフォルトの `READ COMMITTED` 分離レベルでは、リトライ時に新しいスナップショットが取られ、A の行が見えるので `WHEN MATCHED` 分岐が実行されます。明示的なトランザクションの中でステートメントだけをリトライしても機能しません。`MERGE` をセーブポイントで囲んでそこまでロールバックしない限り、エラーの時点でトランザクションはすでに中断されているからです。あるいは、その経路では `ON CONFLICT` を使い続けてください。 ## 2. 「cannot affect row a second time」 同じキーを 2 回含むバッチ upsert は、全体が失敗します。 ```sql INSERT INTO products (sku, name, price) VALUES ('MN-27', 'Monitor', 199.00), ('MN-27', 'Monitor 27"', 189.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price; ``` ```text ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values. ``` これは意図的な挙動です。Postgres は 2 つの行のどちらを優先すべきかを推測しないので、両方の適用を拒否します。同じキーが複数回現れるイベントストリームや CSV ファイルから組み立てたバッチで起きます。挿入の前に重複を除き、どの行を優先するかを決めてください。ここではバッチ内で後の行を優先します。 ```sql INSERT INTO products (sku, name, price) SELECT DISTINCT ON (sku) sku, name, price FROM (VALUES ('MN-27', 'Monitor', 199.00, 1), ('MN-27', 'Monitor 27"', 189.00, 2)) AS batch(sku, name, price, seq) ORDER BY sku, seq DESC ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price RETURNING sku, name, price; ``` ```text sku | name | price -------+-------------+-------- MN-27 | Monitor 27" | 189.00 ``` `MERGE` にも同じルールがあり、メッセージが異なります。2 つのソース行が 1 つの既存ターゲット行にマッチする場合です。 ```sql MERGE INTO products AS p USING (VALUES ('KB-01', 'Keyboard', 44.00), ('KB-01', 'Keyboard', 43.00)) AS f(sku, name, price) ON p.sku = f.sku WHEN MATCHED THEN UPDATE SET price = f.price WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price); ``` ```text ERROR: MERGE command cannot affect row a second time HINT: Ensure that not more than one source row matches any one target row. ``` 重複したキーがまだテーブルに存在しない場合、`MERGE` は `WHEN NOT MATCHED` 分岐を 2 回たどり、代わりに 2 回目の挿入が重複キーエラーで失敗します。どちらの場合も修正方法は同じで、キーごとにソース行を 1 つにすることです。 ## 3. `RETURNING` が空で返る 「なければ作成する」を書く自然な方法は `DO NOTHING` で、ID を取得する自然な方法は `RETURNING id` です。しかし、すでに存在する行については何も返りません。 ```sql INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00) ON CONFLICT (sku) DO NOTHING RETURNING id; ``` ```text id ---- (0 rows) ``` `RETURNING` は、実際に挿入または更新された行しか返しません。常に ID を取得するには、同じステートメント内で既存の行を読みます。 ```sql WITH ins AS ( INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00) ON CONFLICT (sku) DO NOTHING RETURNING id ) SELECT id FROM ins UNION ALL SELECT id FROM products WHERE sku = 'KB-01' LIMIT 1; ``` ```text id ---- 1 ``` 1 つだけ穴が残ります。別のセッションが同じ SKU を挿入してまだコミットしていない場合、`INSERT` はそれを待って何もしませんが、`SELECT` の部分はステートメント開始時に取得したスナップショットを使うので新しい行も見えず、結果は 0 行になります。空の結果は「もう一度試す」の合図として扱ってください。代替案の `DO UPDATE SET sku = EXCLUDED.sku` は常に行を返しますが、毎回新しい行バージョンを書き込みます。これはセクション 5 の問題です。 ## 4. 「there is no unique or exclusion constraint matching the ON CONFLICT specification」 `ON CONFLICT (column)` は「この列の重複をチェックする」という意味ではありません。「まさにこれらの列に対するユニークインデックスを見つけて使う」という意味です。そうしたインデックスのない列を指定すると、ステートメントは何もしないうちに失敗します。 ```sql INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00) ON CONFLICT (name) DO NOTHING; ``` ```text ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ``` わかりにくいのは部分ユニークインデックスの場合です。これは、論理削除されていないアカウントの間でメールアドレスをユニークに保つための一般的な方法です。 ```sql CREATE TABLE accounts ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, deleted_at timestamptz ); CREATE UNIQUE INDEX accounts_live_email ON accounts (email) WHERE deleted_at IS NULL; INSERT INTO accounts (email) VALUES ('ana@example.com') ON CONFLICT (email) DO NOTHING; ``` ```text ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification ``` インデックスは存在しますが、`deleted_at IS NULL` の行しか対象にしていないので、競合ターゲットで同じ条件を指定しない限り Postgres はそれを使いません。 ```sql INSERT INTO accounts (email) VALUES ('ana@example.com') ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING; ``` ```text INSERT 0 1 ``` ORM が `ON CONFLICT` 句を生成している場合は、その `WHERE` を出力できるかどうかを確認してください。ORM に合わせるためだけに部分インデックスを通常のユニークインデックスに置き換えないでください。ルールが変わってしまい、論理削除されたアカウントがメールアドレスを使用中のまま保持することになります。 ## 5. 変わっていない行を書き換える upsert `DO UPDATE` は、競合があればすべての値が同じでも行を更新します。Postgres では、更新は新しい行バージョンを書き込み、古いバージョンは VACUUM が片付けなければならないデッドタプルになります。これは `xmin`（現在のバージョンを書き込んだトランザクションの ID）で確認できます（数値は環境によって異なります）。 ```sql SELECT xmin, price FROM products WHERE sku = 'KB-01'; INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price; SELECT xmin, price FROM products WHERE sku = 'KB-01'; ``` ```text xmin | price ------+------- 742 | 45.00 xmin | price ------+------- 753 | 45.00 ``` 価格は同じなのに、行バージョンは新しくなっています。変更のない 100 万行を毎時間送り直す同期ジョブなら、1 時間に 100 万のデッドタプルが生まれ、さらに WAL と、更新時に発火するトリガーの分も加わります。何も変えない更新はスキップしましょう。 ```sql INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00) ON CONFLICT (sku) DO UPDATE SET name = EXCLUDED.name, price = EXCLUDED.price WHERE (products.name, products.price) IS DISTINCT FROM (EXCLUDED.name, EXCLUDED.price) RETURNING id; ``` ```text id ---- (0 rows) ``` `xmin` は `753` のままです。`NULL` から、または `NULL` への変更も変更として扱われるように、`<>` ではなく `IS DISTINCT FROM` を使ってください。`RETURNING` が空であることに注意してください。`WHERE` で除外された行はロックされますが更新されないので返されません。これもセクション 3 と同じ話です。 ## 6. ID の欠番 ここまでの操作をすべて終えると、テーブルには 4 つの製品が入っています。 ```sql SELECT id, sku FROM products ORDER BY id; ``` ```text id | sku ----+------- 1 | KB-01 3 | WC-01 5 | HS-01 9 | MN-27 ``` `INSERT ... ON CONFLICT` は、競合をチェックする前に、次の ID 値を含めて挿入する予定の行を組み立てます。最終的に更新になった upsert、何もしなかった `DO NOTHING`、失敗した挿入の試みは、どれも ID を 1 つ消費しました。セクション 4 の `ON CONFLICT (name)` のエラーのように、何かを挿入しようとする前に失敗したステートメントは消費していません。[シーケンスのドキュメント](https://www.postgresql.org/docs/17/functions-sequence.html)はまさにこのケースを取り上げています。`WHEN MATCHED` に該当した `MERGE` は ID を採番しませんが、失敗した `MERGE` の挿入は採番します。ID 4 はセクション 1 のセッション B が使いました。 ID を件数として扱うものがない限り、これは無害です。トラフィックの多い upsert 経路が 32 ビットの `integer` の ID を予定より速く使い切りそうなら、上のテーブルのように `bigint` を使ってください。 ## `ON CONFLICT` にはできず、`MERGE` にできること ここまでの内容では、`MERGE` のほうがリスクの高い選択肢に見えます。しかし、別の用途、つまり完全なソースとテーブルを突き合わせる用途では、`MERGE` のほうが優れています。サプライヤーが毎晩完全な価格表を送ってきて、それをステージングテーブルに読み込むとします。 ```sql CREATE TABLE price_feed ( sku text PRIMARY KEY, name text NOT NULL, price numeric(10,2) NOT NULL ); INSERT INTO price_feed VALUES ('KB-01', 'Keyboard', 42.00), ('MN-27', 'Monitor 27"', 189.00), ('HS-01', 'Headset', 79.00), ('MS-01', 'Mouse', 19.00); ``` 1 つの `MERGE` で、変更されたものを更新し、新しいものを挿入し、サプライヤーが削ったものを削除し、各行に 3 つのうちどれが起きたかを報告できます。 ```sql MERGE INTO products AS p USING price_feed AS f ON p.sku = f.sku WHEN MATCHED AND (p.name, p.price) IS DISTINCT FROM (f.name, f.price) THEN UPDATE SET name = f.name, price = f.price WHEN NOT MATCHED THEN INSERT (sku, name, price) VALUES (f.sku, f.name, f.price) WHEN NOT MATCHED BY SOURCE THEN DELETE RETURNING merge_action(), p.sku, p.price; ``` ```text merge_action | sku | price --------------+-------+------- UPDATE | KB-01 | 42.00 UPDATE | HS-01 | 79.00 INSERT | MS-01 | 19.00 DELETE | WC-01 | 59.00 ``` `MN-27` が出力にないのは、変更されていないからです。このステートメントのうち 3 つの要素は [Postgres 17](https://www.postgresql.org/docs/17/release-17.html) で新しく追加されたものです。`MERGE` の `RETURNING`、`merge_action()` 関数、そして `WHEN NOT MATCHED BY SOURCE` です。Postgres 16 では、`MERGE ... RETURNING` は構文エラーになります。17 より前は、同じ同期に upsert と、別の `DELETE ... WHERE NOT EXISTS` と、何が変わったかを報告するためのいくらかの記録処理が必要でした。 `WHEN NOT MATCHED BY SOURCE THEN DELETE` は、ソースにないテーブルの行をすべて削除します。一部しか含まないフィードや、読み込みに失敗した後の空のステージングテーブルに対して実行すると、残すつもりだった行が削除されます。実行前にフィードの行数を確認するか、ターゲットの列に対する条件を追加してください。たとえば `WHEN NOT MATCHED BY SOURCE AND p.supplier = 'acme' THEN DELETE` のようにします。 セクション 1 の並行性に関する注意は、ここでも当てはまります。夜間の同期は通常、それらのキーに書き込む唯一の処理なので、`MERGE` が適しています。同期の実行中にリクエストのトラフィックが同じ SKU を挿入する可能性があるなら、`23505` で同期をリトライしてください。 ## どちらを使うべきか 1. 同じ新しいキーを同時に挿入する可能性のある書き込み：`INSERT ... ON CONFLICT DO UPDATE`。 2. 更新、挿入、削除を 1 回でまとめて行い、テーブルを完全なソースと突き合わせる場合：`MERGE`。ほかに何かがそれらのキーに書き込むなら、SQLSTATE `23505` でリトライします。 3. 行ごとに挿入・更新・削除のどれだったかを知る必要がある場合：Postgres 17 の `MERGE ... RETURNING merge_action()`。 4. バッチ：どちらのステートメントでも、まずキーで重複を除きます。 5. 取得または作成（get-or-create）：`DO NOTHING` に既存行の読み取りを組み合わせ、0 行は「リトライ」として扱います。 6. 変更のないデータの再送：更新に `WHERE ... IS DISTINCT FROM` を追加します。 7. 部分ユニークインデックス：インデックスの `WHERE` を競合ターゲットで繰り返します。 ## データのコピーで同期をリハーサルする この記事でリスクのあるステートメントは、`DELETE` を含むものです。新しい `MERGE` を本番環境に向ける前に、本番環境のコピーで実行してください。Relyt One では、すべてのブランチが共有のコピーオンライトストレージの上に独自のコンピュートを持つので、データベースのブランチを作成し、そこでステージングテーブルに今夜のフィードを読み込み、`MERGE` を実行し、`merge_action()` の出力を確認できます。カタログの半分を削除しようとしていたら、それはブランチ上でわかり、そのブランチは捨てればよいのです。Relyt One は PostgreSQL 17 を採用しているので、`RETURNING`、`merge_action()`、`WHEN NOT MATCHED BY SOURCE` は上で示したとおりに動作します。 [無料で始める](/auth/signin?ref=blog-postgres-upsert-on-conflict-vs-merge)