2018年6月10日日曜日

PostgreSQLで「あればUPDATE、なければINSERT」のUPSERTをやってみる


オフィス狛 技術部のHammarです。

よくデータベースを操作する処理で、あるキーでデータがあればUPDATE、なければINSERTするーというような場面が多々あると思います。
通常はまずはそのキーで件数をSELECTして、件数が0件ならINSERT処理へ、0件以上ならUPDATE処理へ分岐させるというような感じの処理の流れが、まあよくある感じかなと思います。

じつはこれを1回のクエリで行うことができるのですが、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 (val_id, val_name)
    SELECT val_id, val_name From test_table
    WHERE not exists (SELECT id FROM upsert);

上記のクエリでUPSERT的な動きとなり、自分もこの方法で処理を書いていたのですが、この方法で1つハマったことが起きました。
通常テーブルにレコードが何件かある状態で上記のクエリは問題ないのですが、テーブルにレコードが1件もない(初期状態)の場合、「なければ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;

短くていいですね!
ON CONFLICTにキーを指定して存在確認し、レコードがあればUPDATE、なければINSERTというようになっています。ただこのクエリにも1つ注意点があり、ON CONFLICTに指定する項目はUNIQUE制約がある項目でないとこのクエリはエラーとなってしまいます。なので、テーブルの指定の項目にUNIQUE制約をつけて使うようにしてください(複数項目でUNIQUEとなる場合は複合UNIQUE制約をつける)

CTEを使ったクエリでも本来ならうまくできると思うんですが、自分の環境が悪いのかそもそもの問題なのかは今のところわかっていません。ただCONFLICT機能のほうがクエリ的にもすっきりですし、かなり使えると思うのでPostgresql 9.5以上の環境があればこちらを使ったほうがいいと思います。


0 件のコメント:

コメントを投稿