DBMSにはユニークインデックスがあり、重複レコードをINSERTしようとするとエラーで保護する機能があります。
そして最低限、主キーはユニークです。
テストなどの場合、主キーも含めて同じデータをセットアップしたいケースがあり、INSERTを工夫する必要があります。
重複したらINSERTしない
重複を検出したら、INSERTをとりやめる(古いレコードが勝つ)場合の対応は手軽です。
INSERT INTO some_table
(id, name) VALUES (1, 'test record')
ON CONFLICT DO NOTHING;
このように、ON CONFLICT DO NOTHING句を追加すると、重複レコードが存在しない場合のみINSERTします。
同じ効果を得る方法としてWHERE NOT EXISTSを用いて判定する手もありますが、ON CONFLICT DO NOTHINGは定型句である分手軽です。
重複したらUPDATEする(UPSERT)
重複を検出したら、UPDATEで上書きする(新しいレコードが勝つ、通称UPSERT)場合には、以下のような記述になります。
INSERT INTO some_table
(id, name) VALUES (1, 'test record')
ON CONFLICT (id)
DO UPDATE SET name='test record';
重複を検出する条件((id))とUPDATE句を指定する必要があるため、記述は長くなります。
主キー以外でUPSERTしたい場合には、あらかじめユニークインデックスを作成しておく必要があります。
複数のカラムを用いて判定する複合ユニークキーも適切に動作します。
また、PostgreSQL15からSQL:2013のMERGE句も実装されています。次のような文になります。
MERGE INTO some_table
USING (VALUES (1)) AS i (id)
ON some_table.id = i.id
WHEN MATCHED THEN
UPDATE SET name='test record'
WHEN NOT MATCHED THEN
INSERT (id, name) VALUES (1, 'test record');
MERGEの方が性能が良いようですが、リクエストのレース時の挙動はON CONFLICTの方が安定します。
MERGEにはユニークインデックスは不要ですが、実際にはデータ整合性のため実装することが多いでしょう。
制約
基本的に重複検出はユニークインデックスで実装できますが、値にもとづく条件などより細かく制御する場合には
制約も使えます。
制約を使う場合にはON CONFLICT ON CONSTRAINT <constraint_name>句を用います。
PostgreSQLでは、ユニークインデックスと制約は関連する別オブジェクトである点が分かりづらいため、注意が必要です。
ridgepoleを用いて制約をべき等にセットアップする方法については、 ridgepoleのメリットと使い方で解説しています。
Chuma Takahiro
関連記事
