UPSERT

UPSERTって何だっけ？って思ったので、自分用に調べつつPostgreSQLでの使い方をまとめてみました。

UPSERTって

検索してみると、以下のようなイメージらしいです。

データの新規挿入（INSERT）ができれば挿入を行い、新規挿入ができなければ更新（UPDATE）を行うもの。MERGE文と同様。

データの投入時に既存データがあれば更新して最新化、ということで、システム的にはかなり使い勝手がよさそうな機能です。

UPSERT機能は、INSERT/UPDATEのようにUPSERT文というものがあるわけではないですが、INSERT文のオプションや専用のMERGE文を使うことで、実現できるとのことです。

PostgreSQLでお試し

PostgreSQLでは、以下の二つの方法でUPSERTが可能です。

• INSERT INTO ... ON CONFLICT DO UPDATE SET ...
  (PostgreSQL9.5～)
• MERGE INTO ... USING ... ON ... WHEN [NOT] MATCHED THEN ...
  (PostgreSQL15～)

INSERTの場合

こちらは昔からある方です。
ON CONFLICTで制約名もしくは制約がある列名を指定して、制約に違反した場合にUPDATEに切り替えることができます。

SQLのイメージ

INSERT INTO テーブル名(列名,...) 挿入するデータ
ON CONFLICT 制約がある列名 or ON CONFLICT 制約名
DO UPDATE SET 更新する列名 = EXCLUDED.更新する列名

普通のINSERT文の後ろに 使用する制約(ON CONFLICT ...)と更新する際のSET文(DO UPDATE SET ...)を記載します。

忘れそうなポイントとして、UPDATEのSET文の右辺には VALUESなどで指定した挿入するデータを書くと思います。この時シンプルなSQLでは左辺と右辺が当然同じ名前であるため、区別するために専用のEXCLUDEDというテーブル名が与えられてます。これを書いてあげないとエラーになるので要注意です。

ということで、実際にINSERT ON CONFLICT文を使ってみました。

まずは、制約が必要なので、シンプルに主キーがあるテーブルを作ります。

test=# CREATE TABLE test_tbl(id int PRIMARY KEY, num int);
CREATE TABLE
test=# 
test=# \d test_tbl
              Table "public.test_tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 num    | integer |           |          | 
Indexes:
    "test_tbl_pkey" PRIMARY KEY, btree (id)

test=# 

主キーがあるid列を指定してデータが被った場合は、他の列(num)を更新するクエリを実行します。

test=# INSERT INTO test_tbl VALUES(1,1),(2,2),(3,3)
test-# ON CONFLICT(id)
test-# DO UPDATE SET num = EXCLUDED.num;
INSERT 0 3
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  1 |   1
  2 |   2
  3 |   3
(3 rows)

test=# 
test=# 
test=# INSERT INTO test_tbl VALUES(1,10)
test-# ON CONFLICT(id)
test-# DO UPDATE SET num = EXCLUDED.num;
INSERT 0 1
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  2 |   2
  3 |   3
  1 |  10
(3 rows)

test=# 
test=# INSERT INTO test_tbl VALUES(2,200)
test-# ON CONFLICT ON CONSTRAINT test_tbl_pkey
test-# DO UPDATE SET num = EXCLUDED.num;
INSERT 0 1
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  3 |   3
  1 |  10
  2 | 200
(3 rows)

test=# 

MERGEの場合

MERGE文は数か月前に出た最新版で実装された機能です。
こちらは、Oracle Databaseと同様なUPSERT文となっているみたいです。
文法が複雑な分、色々と条件分けして処理ができます。

SQLのイメージ

MERGE INTO 挿入先のテーブル名
USING 挿入データがあるテーブル AS 別名 ON 結合条件
WHEN [NOT] MATCHED THEN
実行する処理(INSERT/UPDATE/DELETE)

MERGE文では制約などは使いません。
挿入先のテーブルと挿入したいデータを結合し、
結合できたデータ(重複するデータ)と結合できなかったデータ(重複しないデータ)などの条件ごとにINSERT/UPDATE/DELETEなどの実行するアクションを指定する形式です。

実行するアクションは、テーブルの指定部分を除いた INSERT/UPDATE/DELETE文で記述します。

例)

  INSERT (id,num) VALUES(b.id,b.num)
  UPDATE SET num = b.num
  DELETE

今度は、MERGE文を使って先ほどと全く同じSQLを作成して試してみます。
まず、同じテーブルを作ります(MERGE文の場合は主キーが無くても問題ないです)。

test=# CREATE TABLE test_tbl(id int PRIMARY KEY, num int);
CREATE TABLE
test=# 
test=# \d test_tbl
              Table "public.test_tbl"
 Column |  Type   | Collation | Nullable | Default 
--------+---------+-----------+----------+---------
 id     | integer |           | not null | 
 num    | integer |           |          | 
Indexes:
    "test_tbl_pkey" PRIMARY KEY, btree (id)

test=# 

挿入したいデータと一致する行(WHEN MATCHED THEN)は重複してるのでUPDATE文、一致しない行(WHEN NOT MATCHED THEN)は存在しないデータなのでINSERT文を実行するようにします。

• 使用するSQL

MERGE INTO test_tbl a
USING (VALUES(挿入するデータ)) AS b(id,num) ON a.id = b.id
WHEN MATCHED THEN
  UPDATE SET num = b.num
WHEN NOT MATCHED THEN
  INSERT (id,num) VALUES(b.id,b.num);

実行すると、INSERTのときと同じようにUPSERT処理となっていることが確認できます。

test=# MERGE INTO test_tbl a
test-# USING (VALUES(1,1),(2,2),(3,3)) AS b(id,num) ON a.id = b.id
test-# WHEN MATCHED THEN
test-#   UPDATE SET num = b.num
test-# WHEN NOT MATCHED THEN
test-#   INSERT (id,num) VALUES(b.id,b.num);
MERGE 3
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  1 |   1
  2 |   2
  3 |   3
(3 rows)

test=# 
test=# 
test=# MERGE INTO test_tbl a
test-# USING (VALUES(1,10)) AS b(id,num) ON a.id = b.id
test-# WHEN MATCHED THEN
test-#   UPDATE SET num = b.num
test-# WHEN NOT MATCHED THEN
test-#   INSERT (id,num) VALUES(b.id,b.num);
MERGE 1
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  2 |   2
  3 |   3
  1 |  10
(3 rows)

test=# MERGE INTO test_tbl a
test-# USING (VALUES(2,200)) AS b(id,num) ON a.id = b.id
test-# WHEN MATCHED THEN
test-#   UPDATE SET num = b.num
test-# WHEN NOT MATCHED THEN
test-#   INSERT (id,num) VALUES(b.id,b.num);
MERGE 1
test=# SELECT * FROM test_tbl;
 id | num 
----+-----
  3 |   3
  1 |  10
  2 | 200
(3 rows)

test=# 

参考情報

• INSERT INTO ON CONFLICT(日本語ドキュメント)
  https://www.postgresql.jp/document/14/html/sql-insert.html#SQL-ON-CONFLICT

• MERGE文(最新版のため、英語ドキュメントのみ)
  https://www.postgresql.org/docs/current/sql-merge.html