PostgreSQL 17がやってくる(7) MERGE文の改善

# はじめに
にゃーん。
今回はPostgreSQL 17のMERGE文の改善点について調べてみた。

# MERGE文
MERGE文は「テーブルの行を条件付きでINSERT、UPDATE、DELETEする」（[PostgreSQL 15文書のMERGE文](https://www.postgresql.jp/document/15/html/sql-merge.html)の説明文から引用）
Oracle等の商用DBMSでは以前から実装されているSQL文だったが、PostgreSQLでの歴史は新しく、PostgreSQL 15から実装されている。

## MERGE文の例
MERGE文はあるテーブルの内容を別のテーブルにマージするSQL文である。

簡単な例でMERGE文の動作を示す。
以下のような2つのテーブル(source, target)があるとする。
```sql
TABLE source;
 id |     data
----+--------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)
```
以下はsoueceとtargetのidがマッチしないsourceのレコードをtargetに挿入する例である。
```sql
$ psql test -af basic-m1.sql
-- idがマッチしないものをtargetに挿入する
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data);
MERGE 1
TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
  2 | kannai-jiro
(4 rows)
$
```
sourceにあるidが2のレコードはマッチしない（targetにない）ので、sourceのid=2のid, dataの値を使ってtargetに挿入している。

NOT MATCHEDとMATCHEDを両方指定することもできる。
以下の例ではマッチしない場合はsourceのid, dataの値を挿入し、マッチする場合にはsourceのdataの値に更新する。
```sql
$ psql test -af basic-m2.sql
-- idがマッチしないものをtargetに挿入する
-- マッチしたものはdataの値を更新する
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data)
WHEN MATCHED THEN UPDATE SET data = s.data;
MERGE 2
TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  4 | new-tantanmen
  2 | kannai-jiro
  3 | sagami-jiro
(4 rows)

$
```
sourceのid=2のレコードはtargetにはないため、sourceの値(id=2, data=kannnai-jiro)を挿入している。
また、id=1, id=3のレコードはtargetに存在しているため、source側のdataの値により更新されている（id=3のdataがsumojiからsagami-jiroに変わっている）
。
# PostgreSQL 17でのMERGE文の改善点
[Commitfest](https://commitfest.postgresql.org/)(2023-07～2024-03)をざっと見た感じでは、PostgreSQL 17ではMERGE文に対して以下の3つの改善が入るようだ。

1. 更新可能ビューに対するMERGE
1. NOT MATCHED時にSOURCE/TARGETが指定可能
1. MERGE結果のRETURING

## 検証バージョン
今回の検証では、PostgreSQL 17-devel 5/18版(commit 15b4d463083b1afd515f2322de7f1b7496a2c336)を使用した。

## 1.更新可能ビューに対するMERGE
Commitfestページ：[MERGE INTO updatable_view](https://commitfest.postgresql.org/47/4076/)

PostgreSQL 16までは、更新可能ビューに対するMERGE文の実行は拒絶されていた。
PostgreSQL 17からは、更新可能ビューに対するMERGE文も実行可能になった。

例えば、先程の例で挙げたtargetに対して単にORDER BYをかけるシンプルなビュー``target_v``を作成する。
```sql
\d+ target_v
                           View "public.target_v"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain    |
 data   | text    |           |          |         | extended |
View definition:
 SELECT id,
    data
   FROM target
  ORDER BY id;

TABLE target_v;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)
```
PostgreSQL 16までは、このビューに対してMERGE文を実行すると、「ビューに対する操作はサポートしていない」旨のエラーになる。にゃーん。

PostgreSQL 16.3での実行例
```sql
\d+ target_v
                           View "public.target_v"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain    |
 data   | text    |           |          |         | extended |
View definition:
 SELECT id,
    data
   FROM target
  ORDER BY id;

MERGE INTO target_v t
USING source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data)
WHEN MATCHED THEN UPDATE SET data = s.data;
psql:view-m2.sql:14: ERROR:  cannot execute MERGE on relation "target_v"
DETAIL:  This operation is not supported for views.
```

PostgreSQL 17では更新可能なビューに対する操作は成功する。
```sql
\d+ target_v
                           View "public.target_v"
 Column |  Type   | Collation | Nullable | Default | Storage  | Description
--------+---------+-----------+----------+---------+----------+-------------
 id     | integer |           |          |         | plain    |
 data   | text    |           |          |         | extended |
View definition:
 SELECT id,
    data
   FROM target
  ORDER BY id;

MERGE INTO target_v t
USING source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data)
WHEN MATCHED THEN UPDATE SET data = s.data;
MERGE 3
TABLE target_v;
 id |     data
----+---------------
  1 | yoshimura-ya
  2 | kannai-jiro
  3 | sagami-jiro
  4 | new-tantanmen
(4 rows)
```

## 2.NOT MATCHED時にSOURCE/TARGETが指定可能
Commitfestページ：[MERGE ... WHEN NOT MATCHED BY SOURCE](https://commitfest.postgresql.org/47/4092/)

PostgreSQL 16までのMERGE文のWHEN句の構文は以下のようになっていた。
```sql
and when_clause is:

{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } }
```

PostgreSQL 17では、``WHEN NOT MATCHED``の指定方法として、``SOUECE``か``TARGET``を指定可能になった（デフォルトは``TARGET``）
```sql
{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED BY SOURCE [ AND condition ] THEN { merge_update | merge_delete | DO NOTHING } |
  WHEN NOT MATCHED [ BY TARGET ] [ AND condition ] THEN { merge_insert | DO NOTHING } }
```
``SOUECE``指定時にはUPDATEまたはDELETEまたは何もしない、という指定が可能。
``TARGET``指定時にはINSERTまたは何もしない、という指定が可能（これはPostgreSQL 16までと同等）。

### NOT MATCHED BY TARGET指定時
TARGET指定時は従来の（TARGETキーワードのない）``NOT MATCHED``と同じ挙動になる。
```sql
-- idがマッチしないものをtargetに挿入する
-- マッチしたものはdataの値を更新する
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED BY TARGET THEN INSERT (id, data) VALUES (s.id, s.data)
WHEN MATCHED THEN UPDATE SET data = s.data;
MERGE 2
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  4 | new-tantanmen
  2 | kannai-jiro
  3 | sagami-jiro
(4 rows)
```
### NOT MATCHED BY SOURCE指定でDELETE

``NOT MATCHED BY SOURCE``を指定してDELETEを実行した場合には、以下のようになる。
```sql
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN MATCHED THEN UPDATE SET data = s.data;
MERGE 3
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |    data
----+-------------
  3 | sagami-jiro
(1 row)
```
source側とtarget側を比較し、targetだけにある、id=1, 4のレコードが削除される。

## 3.MERGE結果のRETURING
Commitfestページ：[MERGE ... RETURNING](https://commitfest.postgresql.org/47/4168/)

PostgreSQL 17ではMERGE文にRETURNING句が追加された。

PostgreSQL 16までのMERGE文の構文
```sql
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
```
PostgreSQL 17のMERGE文の構文
```sql
[ WITH with_query [, ...] ]
MERGE INTO [ ONLY ] target_table_name [ * ] [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
```
最終行にRETURNING指定が追加されている。

RETURNING指定を追加して実行した例を示す。

```sql
-- idがマッチしないものをtargetに挿入する
-- マッチしたものはdataの値を更新する
TABLE source;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  3 | sumoji
  4 | new-tantanmen
(3 rows)

MERGE INTO target t
USING source s ON t.id = s.id
WHEN NOT MATCHED THEN INSERT (id, data) VALUES (s.id, s.data)
WHEN MATCHED THEN UPDATE SET data = s.data
RETURNING t.*
;
 id |    data
----+-------------
  2 | kannai-jiro
  3 | sagami-jiro
(2 rows)

MERGE 2
TABLE target;
 id |     data
----+---------------
  1 | yoshimura-ya
  4 | new-tantanmen
  2 | kannai-jiro
  3 | sagami-jiro
(4 rows)
```
MERGE文の実行時にRETURNINGで指定した（MERGEの影響を受けたtargetの）レコード内容(id=2, 3)が出力されている。
この結果を別のテーブルに格納することで、MERGEのヒストリっぽいのも作れるかもしれない。

# おわりに
PostgreSQL 15から使えるようになったMERGE文が、PostgreSQL 17でより使いやすく改善されたようだ。
これにより、OracleからPostgresqlへの移行がまた少し楽になるのかもしれない。