こんにちは、かつコーチです。
「レコードがあれば更新、なければ新規登録」という処理を書くとき、まずSELECTで存在チェックをしてからINSERTかUPDATEを分岐させていませんか。
その方法はアプリケーションとDBの往復が増えるうえ、同時アクセスがあると競合状態（複数の処理が同時に同じデータを操作してしまう不整合）が起きるリスクもあります。
PostgreSQLのUPSERT（UPDATE + INSERTの造語で、あれば更新・なければ挿入する処理）を使えば、この分岐を1つのSQL文にまとめられます。
UPSERTとは？
用語の定義：UPSERTとON CONFLICT
UPSERTとは、対象のレコードが既に存在すれば更新し、存在しなければ新規挿入する処理のことです。
PostgreSQLではINSERT ... ON CONFLICTという構文で実現します。
INSERT INTO products (sku, product_name, stock_count)
VALUES ('SKU-001', 'ワイヤレスマウス', 50)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
stock_count = EXCLUDED.stock_count;
ON CONFLICT (sku)は「sku列に一意制約がある前提で、その値が重複したときの挙動」を指定する部分です。
EXCLUDEDは、挿入しようとして競合した「新しい値の行」を指す特別なテーブル名で、DO UPDATE SETの中でだけ使えます。
なぜアプリ側の分岐より良いのか
SELECTで存在確認してからINSERT/UPDATEを分ける方法は、確認から実行までの間に別のリクエストが同じデータを操作すると、意図しない重複データが生まれることがあります。
-- ❌ Before：存在チェックと更新の間に別プロセスが割り込む余地がある
SELECT sku FROM products WHERE sku = 'SKU-001';
-- （ここで別のリクエストが同じSKUをINSERTしてしまうかもしれない）
INSERT INTO products (sku, product_name, stock_count) VALUES ('SKU-001', 'ワイヤレスマウス', 50);
-- ERROR: duplicate key value violates unique constraint
-- ✅ After：1文で完結させ、競合状態を発生させない
INSERT INTO products (sku, product_name, stock_count)
VALUES ('SKU-001', 'ワイヤレスマウス', 50)
ON CONFLICT (sku)
DO UPDATE SET
product_name = EXCLUDED.product_name,
stock_count = EXCLUDED.stock_count;
私が実際に運用しているCSV一括インポート機能でも、以前はSELECTしてから分岐する実装で、まれに重複キーエラーがログに出ていました。
ON CONFLICTに置き換えてからは、同じSKUの行が連続していても1回のクエリで安全に処理できるようになり、エラーがゼロになりました。
基本の書き方
手順1：何もしないパターン（DO NOTHING）
重複した場合は何もせず無視したいだけならDO NOTHINGを使います。
INSERT INTO email_subscribers (email)
VALUES ('user@example.com')
ON CONFLICT (email)
DO NOTHING;
会員登録のメールアドレス重複防止など、「既にあれば何もしない」で十分なケースに向いています。
手順2：一部の列だけ更新する
在庫数だけ加算し、他の列は変更したくない場合は、更新対象を絞り込みます。
INSERT INTO products (sku, product_name, stock_count)
VALUES ('SKU-001', 'ワイヤレスマウス', 10)
ON CONFLICT (sku)
DO UPDATE SET
stock_count = products.stock_count + EXCLUDED.stock_count;
DO UPDATE SETの中では、更新前の値をテーブル名.列名（この例ではproducts.stock_count）、挿入しようとした新しい値をEXCLUDED.列名で書き分けられます。
在庫の「加算」のように、既存値を使った計算をしたいときによく使うパターンです。
手順3：条件付きで更新する（WHERE句の追加）
DO UPDATE SETの後にWHEREを付けると、条件を満たすときだけ更新できます。
INSERT INTO products (sku, product_name, price, updated_at)
VALUES ('SKU-001', 'ワイヤレスマウス', 2980, now())
ON CONFLICT (sku)
DO UPDATE SET
price = EXCLUDED.price,
updated_at = EXCLUDED.updated_at
WHERE products.price IS DISTINCT FROM EXCLUDED.price;
価格が変わっていないのに毎回UPDATEが発生すると、更新日時だけが無駄に書き換わり続けます。
IS DISTINCT FROM（NULLを含んでも安全に「異なるか」を判定する比較演算子）で実際に値が変わったときだけ更新する形にすると、無駄な書き込みを防げます。
よくあるつまずきポイント・エラー対処
ON CONFLICTの対象列には制約が必須
ON CONFLICT (列名)で指定できるのは、一意制約または主キー制約が設定されている列（の組み合わせ）だけです。
-- ❌ Before：一意制約のない列を指定してエラー
INSERT INTO products (sku, product_name)
VALUES ('SKU-001', 'ワイヤレスマウス')
ON CONFLICT (product_name)
DO UPDATE SET sku = EXCLUDED.sku;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification
product_nameに一意制約がなければこのエラーになります。
-- ✅ After：事前に一意制約を追加してから使う
ALTER TABLE products ADD CONSTRAINT products_product_name_key UNIQUE (product_name);
INSERT INTO products (sku, product_name)
VALUES ('SKU-001', 'ワイヤレスマウス')
ON CONFLICT (product_name)
DO UPDATE SET sku = EXCLUDED.sku;
「重複チェックしたい列」と「一意制約を張っている列」が一致しているか、設計段階で確認しておきましょう。
応用・一歩先の使い方
RETURNINGで登録・更新結果をそのまま受け取る
RETURNING句を付けると、INSERT・UPDATE・DELETE・UPSERTの結果をそのままSELECTのように取得できます。
アプリ側で「登録した後、採番されたIDが欲しくてもう一度SELECTする」という往復をなくせます。
INSERT INTO products (sku, product_name, stock_count)
VALUES ('SKU-002', 'USBハブ', 30)
ON CONFLICT (sku)
DO UPDATE SET stock_count = products.stock_count + EXCLUDED.stock_count
RETURNING product_id, sku, stock_count, (xmax = 0) AS is_inserted;
xmax = 0は少しトリッキーですが、「新規挿入ならxmaxが0、更新なら更新前のトランザクションIDが入る」という内部的な性質を利用して、UPSERTの結果が新規挿入か更新かを1回のクエリで判定するテクニックです。
バッチ処理のログで「何件新規登録され、何件更新されたか」を集計したいときに重宝します。
WITH upserted AS (
INSERT INTO products (sku, product_name, stock_count)
VALUES ('SKU-003', 'モニターアーム', 15)
ON CONFLICT (sku)
DO UPDATE SET stock_count = products.stock_count + EXCLUDED.stock_count
RETURNING (xmax = 0) AS is_inserted
)
SELECT
COUNT(*) FILTER (WHERE is_inserted) AS inserted_count,
COUNT(*) FILTER (WHERE NOT is_inserted) AS updated_count
FROM upserted;
前々回紹介したCTEと組み合わせることで、1件のUPSERTだけでなく、集計結果まで含めて1つのクエリで完結させられます。
まとめ
この記事のポイント
INSERT ... ON CONFLICTで「あれば更新、なければ挿入」を1文にまとめられるDO NOTHINGは無視、DO UPDATE SETは更新で、EXCLUDEDが新しい値を指すON CONFLICTの対象列には一意制約または主キー制約が必要RETURNINGとxmax = 0で、新規挿入か更新かを判定できる
次に読むべき記事
クエリ応用編はこれで一区切りです。
次は設計面から、テーブルを整理するスキーマの使い方に進みましょう。
- ウィンドウ関数の基本：ランキングと集計を両立する
- スキーマを使ってテーブルを整理する（近日公開）
タグ: PostgreSQL, 中級者向け, クエリ
