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 制約か一意インデックスを追加するか、すでにある制約の列を指定してください。