Postgres の INSERT ... ON CONFLICT と MERGE：どちらで upsert すべきか、それぞれが出すエラー

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 のドキュメントは「アトミックな INSERT または UPDATE の結果。独立したエラーがない限り、高い並行性のもとでも、2 つの結果のどちらか一方が保証される」と保証しています。夜間のフィードやステージングテーブルのように行の集合全体をテーブルと突き合わせるときで、条件付きの更新や削除、各行に何が起きたかのレポートが必要な場合は MERGE を使います。

Postgres 17 ならどれでも動きます。Docker の場合は次のとおりです。

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 つ作ります。

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 は挿入しようとした行です。

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;

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 49.00 | t

45.00 でもう一度実行すると、同じ行が更新されて返り、inserted は false になります。

 id |  sku  | price | inserted
----+-------+-------+----------
  1 | KB-01 | 45.00 | f

(xmax = 0) の判定は、挿入と更新を見分けるために広く使い回されているテクニックです。挿入されたばかりの行バージョンには xmax がないので機能しますが、ドキュメント化された挙動ではなく、Postgres が行バージョンを格納する仕組みに依存しています。MERGE にはアクションを報告するドキュメント化された方法があり、この記事の最後で紹介します。

psql のセッションを 2 つ開きます。1 つ目でトランザクションを開始し、まだ存在しない SKU を MERGE で upsert しますが、コミットはしません。

-- セッション 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 を実行します。処理が止まります。

-- セッション 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 は次のエラーで再開します。

ERROR:  duplicate key value violates unique constraint "products_sku_key"
DETAIL:  Key (sku)=(WC-01) already exists.

何が起きたのかを説明します。セッション B が開始した時点では A の行はコミットされていなかったので、B の結合はマッチを見つけられず、B は WHEN NOT MATCHED 分岐を選びました。その後 B の挿入は、ユニークインデックス上で A の終了を待ちました。A がコミットした時点でキーは存在していますが、MERGE にはその時点で WHEN MATCHED 分岐に切り替える手段がありません。MERGE のドキュメントもそう述べており、「並行する INSERT が発生した場合に UPDATE を実行できる代替のステートメント」として INSERT ... ON CONFLICT を案内しています。

次に、別の新しい SKU を使って、同じ競合を ON CONFLICT で試します。

-- セッション 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;

-- セッション 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 の価格が入ります。

SELECT sku, price FROM products WHERE sku IN ('WC-01', 'HS-01') ORDER BY sku;

  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 回含むバッチ upsert は、全体が失敗します。

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;

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 ファイルから組み立てたバッチで起きます。挿入の前に重複を除き、どの行を優先するかを決めてください。ここではバッチ内で後の行を優先します。

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;

  sku  |    name     | price
-------+-------------+--------
 MN-27 | Monitor 27" | 189.00

MERGE にも同じルールがあり、メッセージが異なります。2 つのソース行が 1 つの既存ターゲット行にマッチする場合です。

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

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 つにすることです。

「なければ作成する」を書く自然な方法は DO NOTHING で、ID を取得する自然な方法は RETURNING id です。しかし、すでに存在する行については何も返りません。

INSERT INTO products (sku, name, price) VALUES ('KB-01', 'Keyboard', 45.00)
ON CONFLICT (sku) DO NOTHING
RETURNING id;

 id
----
(0 rows)

RETURNING は、実際に挿入または更新された行しか返しません。常に ID を取得するには、同じステートメント内で既存の行を読みます。

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;

 id
----
  1

1 つだけ穴が残ります。別のセッションが同じ SKU を挿入してまだコミットしていない場合、INSERT はそれを待って何もしませんが、SELECT の部分はステートメント開始時に取得したスナップショットを使うので新しい行も見えず、結果は 0 行になります。空の結果は「もう一度試す」の合図として扱ってください。代替案の DO UPDATE SET sku = EXCLUDED.sku は常に行を返しますが、毎回新しい行バージョンを書き込みます。これはセクション 5 の問題です。

ON CONFLICT (column) は「この列の重複をチェックする」という意味ではありません。「まさにこれらの列に対するユニークインデックスを見つけて使う」という意味です。そうしたインデックスのない列を指定すると、ステートメントは何もしないうちに失敗します。

INSERT INTO products (sku, name, price) VALUES ('MS-01', 'Mouse', 19.00)
ON CONFLICT (name) DO NOTHING;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

わかりにくいのは部分ユニークインデックスの場合です。これは、論理削除されていないアカウントの間でメールアドレスをユニークに保つための一般的な方法です。

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;

ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

インデックスは存在しますが、deleted_at IS NULL の行しか対象にしていないので、競合ターゲットで同じ条件を指定しない限り Postgres はそれを使いません。

INSERT INTO accounts (email) VALUES ('ana@example.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;

INSERT 0 1

ORM が ON CONFLICT 句を生成している場合は、その WHERE を出力できるかどうかを確認してください。ORM に合わせるためだけに部分インデックスを通常のユニークインデックスに置き換えないでください。ルールが変わってしまい、論理削除されたアカウントがメールアドレスを使用中のまま保持することになります。

DO UPDATE は、競合があればすべての値が同じでも行を更新します。Postgres では、更新は新しい行バージョンを書き込み、古いバージョンは VACUUM が片付けなければならないデッドタプルになります。これは xmin（現在のバージョンを書き込んだトランザクションの ID）で確認できます（数値は環境によって異なります）。

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

 xmin | price
------+-------
  742 | 45.00

 xmin | price
------+-------
  753 | 45.00

価格は同じなのに、行バージョンは新しくなっています。変更のない 100 万行を毎時間送り直す同期ジョブなら、1 時間に 100 万のデッドタプルが生まれ、さらに WAL と、更新時に発火するトリガーの分も加わります。何も変えない更新はスキップしましょう。

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;

 id
----
(0 rows)

xmin は 753 のままです。NULL から、または NULL への変更も変更として扱われるように、<> ではなく IS DISTINCT FROM を使ってください。RETURNING が空であることに注意してください。WHERE で除外された行はロックされますが更新されないので返されません。これもセクション 3 と同じ話です。

ここまでの操作をすべて終えると、テーブルには 4 つの製品が入っています。

SELECT id, sku FROM products ORDER BY id;

 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) のエラーのように、何かを挿入しようとする前に失敗したステートメントは消費していません。シーケンスのドキュメントはまさにこのケースを取り上げています。WHEN MATCHED に該当した MERGE は ID を採番しませんが、失敗した MERGE の挿入は採番します。ID 4 はセクション 1 のセッション B が使いました。

ID を件数として扱うものがない限り、これは無害です。トラフィックの多い upsert 経路が 32 ビットの integer の ID を予定より速く使い切りそうなら、上のテーブルのように bigint を使ってください。

ここまでの内容では、MERGE のほうがリスクの高い選択肢に見えます。しかし、別の用途、つまり完全なソースとテーブルを突き合わせる用途では、MERGE のほうが優れています。サプライヤーが毎晩完全な価格表を送ってきて、それをステージングテーブルに読み込むとします。

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 つのうちどれが起きたかを報告できます。

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;

 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 で新しく追加されたものです。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 は上で示したとおりに動作します。

無料で始める