Menu

PostgreSQL の upsert: INSERT ... ON CONFLICT の使い方

PostgreSQL の upsert は INSERT ... ON CONFLICT で書きます。EXCLUDED を使う DO UPDATE、DO NOTHING、条件付きの更新、カウンター、挿入された行と更新された行の見分け方を扱います。

このページのコードはエディタで実行できます。編集してすぐに結果を確認できます。

PostgreSQL に UPSERT というキーワードはありません。INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column と書きます。新しい行がその列の一意制約に違反する場合、PostgreSQL は代わりに既存の行を更新します。DO NOTHING ならスキップします。

A-100 はすでにあったので、価格が 12.5 に変わりました。B-200 は新しかったので挿入されました。1 つの文で両方を処理でき、ほかのセッションが同時に同じキーを挿入しても正しく動きます。

1 行を挿入または更新する

文は 3 つの部分からなります。

INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
  • 競合ターゲット には、主キーなどの一意インデックスか一意制約の列を指定します。完全に一致する必要があり、一致しないと PostgreSQL は "there is no unique or exclusion constraint matching the ON CONFLICT specification" というエラーを出します。代わりに制約名を指定することもできます: ON CONFLICT ON CONSTRAINT products_pkey。
  • EXCLUDED は挿入しようとした行です。EXCLUDED.price は新しい価格、products.price はすでに保存されている価格です。
  • DO UPDATE SET は指定した列だけを変更します。既存の行のほかの列は値を保ちます。

WHERE 句付きの一意インデックス (部分インデックス) も、その条件を繰り返して書けばターゲットにできます: ON CONFLICT (email) WHERE deleted_at IS NULL。

DO NOTHING で重複をスキップする

DO NOTHING は新しい行だけを挿入し、競合する行はエラーを出さずに捨てます。競合ターゲットを書かなければ、どの一意制約に違反する行もスキップします。

RETURNING には lin@example.com だけが表示されます。スキップされた行は返らないので、DO NOTHING ... RETURNING でどの行が新しかったかがわかります。

カウンターに加算する

保存されている値を置き換えずに増やすには、現在の値に EXCLUDED を足します。現在の値はテーブル名で修飾してください。DO UPDATE の中で単に views と書くと、テーブルと EXCLUDED の両方に一致し、PostgreSQL は "column reference "views" is ambiguous" で止まります。

1 つ目の文が 1 で行を挿入し、続く 2 つが加算するので、/home は最終的に 3 になります。

値が変わったときだけ更新する

DO UPDATE SET のあとの WHERE は、行ごとに更新を実行するかを決めます。false の場合、行はそのまま残り、挿入にも更新にも数えられません。最新のデータだけを残したいときや、何も変わらないのに新しい行バージョンを書くのを避けたいときに使います。

挿入しようとした行のほうが古いので、A-100 は 10 のままです。B-200 は 25 になります。同じ値を書くだけの更新をスキップするには WHERE prices.price IS DISTINCT FROM EXCLUDED.price を使います。これは NULL も正しく扱います。

挿入された行と更新された行を見分ける

RETURNING は、普通の INSERT と同じく upsert でも使えます。2 つの場合を見分けるには、システム列 xmax を読みます。この文で挿入された行では 0 です。

A-100 は 8 で inserted が false、C-300 は 7 で true として返ります。xmax は内部の仕組みで、ドキュメント化された API ではないので、便利な裏技として扱ってください。この句の詳細は RETURNING のページ で扱います。

よく出るエラー

  • "ON CONFLICT DO UPDATE command cannot affect row a second time": 1 つの文の VALUES や SELECT に同じキーが 2 回現れています。SELECT DISTINCT ON (sku) ... などで先に重複を取り除いてください。
  • "column reference is ambiguous": 上の例のように、SET の式と WHERE で列をテーブル名で修飾してください。
  • identity や serial の id の欠番: 更新に変わった INSERT もシーケンスから値を取っているので、id が飛びます。これは想定どおりの動作です。

MERGE (PostgreSQL 15 以降) でも挿入または更新ができますが、2 つのセッションが同時に同じキーを挿入する場合への備えはありません。キーによる upsert には ON CONFLICT を使ってください。すべてのオプションは 公式の INSERT リファレンス に載っています。

よくある質問

PostgreSQL に UPSERT 文はありますか?

UPSERT というキーワードはありません。PostgreSQL 9.5 で INSERT ... ON CONFLICT が追加されました。行を挿入し、一意制約に違反する場合は既存の行を更新 (DO UPDATE) するか、スキップ (DO NOTHING) します。

INSERT ... ON CONFLICT は同時に挿入しても安全ですか?

安全です。ほかのセッションが同じ瞬間に同じキーを挿入しても、挿入しようとした各行は必ず挿入か更新のどちらかになります。アプリケーションのコードで SELECT のあとに INSERT や UPDATE を実行する方法では、この保証は得られません。

upsert には MERGE と ON CONFLICT のどちらを使うべきですか?

キーによる挿入または更新なら ON CONFLICT を使ってください。MERGE (PostgreSQL 15 以降) は、一致した行の削除など、より多くの場合を扱えます。ただし同じキーが同時に挿入されると一意制約違反で失敗することがあり、ON CONFLICT ならこれを避けられます。

"there is no unique or exclusion constraint matching the ON CONFLICT specification" というエラーが出るのはなぜですか?

ON CONFLICT (...) の列は、一意インデックスか一意制約と完全に一致する必要があります。その列に UNIQUE 制約か一意インデックスを追加するか、すでにある制約の列を指定してください。

Coddyのプログラミング言語のイラスト

Coddyでコードを学ぼう

始める