UPSERT

レコードがあれば UPDATE、なければ INSERT という基本的な処理を行うことを、UPSERT という俗称で呼ぶ。

基本的な処理であるはずなのに、どうして SQL に標準実装されていないのか?

SQL は「INSERT, UPDATE, DELETE, SELECT がデータ操作の基本単位で、組み合わせればすべてのデータ操作が可能なはず」という単純な思想で作られてしまった。(直感的には正しい)

実際の非同期で必要とされる運用まで考えていなかった。

PostgreSQL 9.5 以降

CONFLICT 句を使う。(SQL発行1回)

競合する場合もレコードは失われない。更新順序も正しく実行される。

code:upsert_new.sql

-- レコードを挿入してみて、重複なら更新

INSERT INTO mytable VALUES (:key_value, :field_value)

ON CONFLICT ON CONSTRAINT mytable_constraint

DO UPDATE

SET

myfield = :field_value

WHERE 句は要らない。(衝突したレコードに対して更新される。)

汎用

UPDATE 後、SELECT INSERT 方式を使う。(SQL発行2回)

競合する場合は後の方は失敗してそのレコードは失われる。(先勝ち)

先勝ちなので、微妙に問題がある。(UPSERTで期待する動きは後勝ち。そのため、整合しなくなる可能性がある。)

code:upsert_old.sql

-- レコードがあれば更新、なければ何も起こらない

UPDATE mytable

SET

myfield = :field_value

WHERE

mykey = :key_value;

-- ここで更新件数が取れて1なら成功でよい。

-- 以下、更新件数が取れない系での対策

-- レコードがない時のみ挿入

INSERT INTO mytable (mykey, myfield)

SELECT :key_value, :field_value

WHERE NOT EXISTS (SELECT 1 FROM mytable WHERE mykey = :key_value);

INSERT の失敗を自力で判定するのではだめなのか?

INSERT の失敗がトランザクションの失敗にならないなら可能。

PostgreSQL の場合、問い合わせの失敗はトランザクションの失敗と判定されてしまうため、セーブポイントを INSERT 前に作ってから INSERT を行ってロールバックすれば可能。

9.5 以降は CONFLICT 句を使えばよいので、わざわざこういう実装をする必要がない。

参考