PostgreSQLでINSERTとUPDATEを1行で実行するMERGE（UPSERT）を行う

PostgreSQL でキーで検索した結果でデータが存在しなければ挿入、存在すれば更新のような処理を行うことがよくあります。

SQLServer では MERGE 文を使えば IF 文を書く必要はなく行えますが、PostgreSQL には MERGE 文はありません。

その代わりに CONFLICT という機能で実現できるようになっています。

PostgreSQLでMERGE（UPSERT）を行う

PostgreSQL で MERGE 文を実装するには CONFLICT を使います。

CONFLICT は ON CONFLICT にキーを指定し、そのキーのレコードがなければ挿入、あれば更新という MERGE と同じことを行ってくれます。

CONFLICTの使い方

CONFLICT は INSERT 文で ON CONFLICT 句でキーを指定し使います。

まずはテスト用のテーブルを用意します。

CREATE TABLE UPSERT_TEST
(
    id integer
    ,name varchar(20)
    ,age integer
    ,gender integer
    ,PRIMARY KEY (id)
)

次にテスト用のデータを用意します。

INSERT INTO UPSERT_TEST VALUES ( 1,'name1', 10, 0 )
                              ,( 2,'name2', 20, 1 )
                              ,( 3,'name3', 30, 0 ) ;
SELECT * FROM UPSERT_TEST ORDER BY id ;

上記の実行結果は以下の通りになります。

1    "name1"    10    0
2    "name2"    20    1
3    "name3"    30    0

ここで ON CONFLICT を指定して INSERT 文を発行します。

INSERT INTO UPSERT_TEST ( id, name, age, gender )
    VALUES ( 4, 'name4', 40, 1 )
    ON CONFLICT (id)
    DO UPDATE SET name = 'name4_update' ;
SELECT * FROM UPSERT_TEST ORDER BY id ;

上記の SQL の実行結果は以下のようになり

1    "name1"    10    0
2    "name2"    20    1
3    "name3"    30    0
4    "name4"    40    1

name フィールドが「name4」で新規にデータが追加されていることがわかります。

このまま再度同じ Insert 文を実行します。

INSERT INTO UPSERT_TEST ( id, name, age, gender )
    VALUES ( 4, 'name4', 40, 1 )
    ON CONFLICT (id)
    DO UPDATE SET name = 'name4_update' ;
SELECT * FROM UPSERT_TEST ORDER BY id ;

再度同じ INSERT 文を発行した場合の結果は

1    "name1"           10    0
2    "name2"           20    1
3    "name3"           30    0
4    "name4_update"    40    1

となり、今度は name フィールドが「name4_update」で更新されています。

無事 MERGE 文と同じことができました。

まとめ

PostgreSQL では SQLServer の MERGE 文と同じことを行うには CONFLICT を使って行います。

SQLServerでMERGE文を実行する方法

SQLServerのMERGEでINSERTとUPDATEを1行で実行する

SQLServerのMERGE文を利用すると、指定した条件に応じて挿入(INSERT)、更新(UPDATE)、削除(DELETE)を実行することが1行のSQLで可能です。

lightgauge.net