オフィス狛 技術部のHammarです。
よくデータベースを操作する処理で、あるキーでデータがあればUPDATE、なければINSERTするーというような場面が多々あると思います。
通常はまずはそのキーで件数をSELECTして、件数が０件ならINSERT処理へ、0件以上ならUPDATE処理へ分岐させるというような感じの処理の流れが、まあよくある感じかなと思います。
じつはこれを１回のクエリで行うことができるのですが、PostgresqlやMysql等でやり方がそれぞれ違います。
今回はPostgresqlでのやり方で、自分が少しハマった部分や注意点も含めて書こうと思います。
■UPSERT
「データがあればUPDATE、なければINSERTする」という処理はUPSERTと呼ばれています。INSERT（追加）とUPDATE（更新）の両方の機能を併せ持っていることから、UPDATE + INSERTで、UPSERTということのようです。PostgresqlはバージョンによってこのUPSERTのやり方が変わってきます。
Postgresql 9.5以上でCONFLICTというUPSERTができる機能が備わりましたが、それより前のバージョンではこの機能が使えないので、CTE（共通テーブル式）という構文を利用して下記のようなクエリでUPSERT的なことが可能です。
（ちなみにCTEとはこちらに詳しく書かれています）
■CTEを使ったUPSERT的なクエリ
WITH upsert AS (
UPDATE test_table
SET
name = val_name
WHERE
id = val_id
RETURNING val_id
)
INSERT INTO test_table (id, name)
SELECT val_id, val_name
WHERE not exists (SELECT id FROM upsert);
UPDATE test_table
SET
name = val_name
WHERE
id = val_id
RETURNING val_id
)
INSERT INTO test_table (id, name)
SELECT val_id, val_name
WHERE not exists (SELECT id FROM upsert);
上記のクエリでUPSERT的な動きとなり、自分もこの方法で処理を書いていたのですが、この方法で１つハマったことが起きました。
通常テーブルにレコードが何件かある状態で上記のクエリは問題ないのですが、テーブルにレコードが１件もない（初期状態）の場合、「なければINSERT」となるので本来ならレコードが登録されるはずなのですが、なぜか上記のクエリではレコードが登録されず、テーブルは空のままでした。
原因は不明なのですが、恐らくSELECT句でNULL判定できずに登録するものがないと判断されるようです。（ただ、試しにNOT NULLでやってもうまくいきませんでした）
ということで上記の方法は厳密には使えないことがわかったので、どうしようかとちょっと悩みましたが自分の開発環境がPostgresql 9.5以上であることがわかり、ならばあたらしく備わったCONFLICTという機能を使ってみようということで、下記のようなクエリを作成します。
■CONFLICT機能を利用したクエリ
INSERT INTO test_table (id, name)
VALUES (val_id, val_name)
ON CONFLICT (id)
DO UPDATE SET name = val_name；
VALUES (val_id, val_name)
ON CONFLICT (id)
DO UPDATE SET name = val_name；
短くていいですね！
ON CONFLICTにキーを指定して存在確認し、レコードがあればUPDATE、なければINSERTというようになっています。ただこのクエリにも１つ注意点があり、ON CONFLICTに指定する項目はUNIQUE制約がある項目でないとこのクエリはエラーとなってしまいます。なので、テーブルの指定の項目にUNIQUE制約をつけて使うようにしてください（複数項目でUNIQUEとなる場合は複合UNIQUE制約をつける）
CTEを使ったクエリでも本来ならうまくできると思うんですが、自分の環境が悪いのかそもそもの問題なのかは今のところわかっていません。ただCONFLICT機能のほうがクエリ的にもすっきりですし、かなり使えると思うのでPostgresql 9.5以上の環境があればこちらを使ったほうがいいと思います。
PostgreSQL
最後の部分はこんな感じではないでしょうか？
返信削除WITH upsert AS (...)
INSERT INTO test_table (id,name)
SELECT val_id, val_name
WHERE not exists (SELECT 1 FROM upsert);
ご指摘ありがとうございます！
削除確かに「CTEを使ったUPSERT的なクエリ」のInsert文が一部間違えておりましたので、修正いたしました！