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