アップサート（INSERT ... ON CONFLICT）
PostgreSQL で INSERT ... ON CONFLICT を使ってアップサートするには？
INSERT ... ON CONFLICT は、行を挿入するか、一意制約に違反する場合は既存の行を更新またはスキップする処理を、1 つのアトミックな操作として行います。
アップサート（upsert）とは、挿入（insert）または更新（update）を意味します。PostgreSQL では INSERT ... ON CONFLICT で実装されています。新しい行が一意制約または排他制約に違反する場合、DO NOTHING はその行をスキップし、DO UPDATE は代わりに競合した行を更新します。
ON CONFLICT DO UPDATE は、同時実行されるセッションがあっても、挿入または更新のどちらかがアトミックに行われることを保証するため、SELECT の後に INSERT または UPDATE を別途実行する必要はありません。特別なテーブル EXCLUDED は、挿入が提案された行を参照します。(path) のような競合対象は、一意インデックスまたは制約と一致している必要があります。
例
この例では、同じアップサートを 2 回実行します。1 回目は /home を閲覧数 1 で挿入し、2 回目は主キーと競合して 1 を加算するため、最終的な件数は 2 になります。
CREATE TABLE page_views (path text PRIMARY KEY, views bigint NOT NULL);
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
INSERT INTO page_views VALUES ('/home', 1)
ON CONFLICT (path) DO UPDATE SET views = page_views.views + EXCLUDED.views;
SELECT * FROM page_views;拡張機能なしの標準の PostgreSQL 16 または 17 で動くプレーンな SQL です。破棄してかまわない空のデータベースで実行してください。
出典
PostgreSQL 公式ドキュメント（英語）。