【PostgreSQL】ON CONFLICTでUPSERT（INSERT or UPDATE）を1文で書く

ON CONFLICTとは

PostgreSQLのINSERT文にはON CONFLICT句があり、一意制約や主キー制約に違反した場合の挙動を指定できる。ON CONFLICT句を使うと、行が存在しなければINSERT、既に存在すればUPDATEするUPSERT処理を1つの文で記述できる。

以下のテーブルで動作を確認する。

CREATE TABLE items (
  id integer PRIMARY KEY,
  name text NOT NULL,
  stock integer NOT NULL DEFAULT 0,
  updated_at timestamp NOT NULL DEFAULT now()
);

INSERT INTO items (id, name, stock) VALUES (1, 'apple', 10);

ON CONFLICT DO UPDATEで更新する

主キーidが競合した場合にstockを加算するには以下のように記述する。

INSERT INTO items (id, name, stock)
VALUES (1, 'apple', 5)
ON CONFLICT (id)
DO UPDATE SET stock = items.stock + EXCLUDED.stock, updated_at = now();

 id | name  | stock |         updated_at         
----+-------+-------+----------------------------
  1 | apple |    15 | 2026-07-23 12:17:49.716479

stockが10から15に更新され、INSERTで渡した5が加算されている。ON CONFLICT (id)には競合を検出する対象の列を指定する。DO UPDATE SETには更新内容を指定し、EXCLUDEDテーブルでINSERTしようとした値を参照できる。items.stockは既存の行の値を指す。

ON CONFLICT (id)の対象列には、主キーや一意制約が設定された列を指定する必要がある。制約のない列を指定すると以下のエラーになる。

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

制約名で指定する

対象列の代わりに、制約名をON CONFLICT ON CONSTRAINTで指定できる。複合主キーや複数列の一意制約の場合に使う。

CREATE TABLE stocks (
  warehouse_id integer NOT NULL,
  item_id integer NOT NULL,
  quantity integer NOT NULL DEFAULT 0,
  CONSTRAINT stocks_pk PRIMARY KEY (warehouse_id, item_id)
);

INSERT INTO stocks (warehouse_id, item_id, quantity) VALUES (1, 100, 10);

INSERT INTO stocks (warehouse_id, item_id, quantity) VALUES (1, 100, 5)
ON CONFLICT ON CONSTRAINT stocks_pk
DO UPDATE SET quantity = stocks.quantity + EXCLUDED.quantity;

 warehouse_id | item_id | quantity 
--------------+---------+----------
            1 |     100 |       15

ON CONFLICT (warehouse_id, item_id)と列を並べて指定しても同じ結果になる。

ON CONFLICT DO NOTHINGで無視する

競合時に何もしたくない場合はDO UPDATEの代わりにDO NOTHINGを指定する。

INSERT INTO items (id, name, stock) VALUES (1, 'apple', 100)
ON CONFLICT (id) DO NOTHING;

INSERT 0 0

競合した行のstockは更新されず、影響を受けた行数が0件であることを示すINSERT 0 0と表示される。既に存在するデータを重複登録せずスキップしたい場合に使う。

WHERE句で更新条件を絞り込む

DO UPDATE SETの後にWHERE句を追加すると、条件を満たす場合のみ更新できる。stockが10未満の場合のみ加算するには以下のように記述する。

INSERT INTO items (id, name, stock) VALUES (1, 'apple', 3)
ON CONFLICT (id)
DO UPDATE SET stock = items.stock + EXCLUDED.stock
WHERE items.stock < 10;

items.stockは15であり条件を満たさないため、INSERT 0 0となり更新されない。WHERE句ではitems（既存の行）とEXCLUDED（INSERTしようとした値）の両方を参照できる。

RETURNINGとxmaxでINSERTとUPDATEを判別する

UPSERTの実行結果がINSERTとUPDATEのどちらだったかをアプリケーション側で判別したい場合、擬似カラムxmaxをRETURNINGで取得する方法がある。新規挿入された行のxmaxは0になるため、xmax = 0でINSERTかUPDATEかを判定できる。

INSERT INTO items (id, name, stock) VALUES (3, 'cherry', 1)
ON CONFLICT (id) DO UPDATE SET stock = items.stock + EXCLUDED.stock
RETURNING id, name, stock, (xmax = 0) AS inserted;

1回目の実行では新規INSERTのためinsertedがtになる。

 id |  name  | stock | inserted 
----+--------+-------+----------
  3 | cherry |     1 | t

同じ文をもう一度実行すると、既存行へのUPDATEになりinsertedはfになる。

 id |  name  | stock | inserted 
----+--------+-------+----------
  3 | cherry |     2 | f

xmaxはPostgreSQL内部で行を削除・更新したトランザクションIDを保持する擬似カラムである。挙動は将来のバージョンで変わる可能性がある非公式なテクニックである点に注意する。

≫ INSERT