PostgreSQL 15で追加されたMERGEを使ってみたよ

この記事は ミライトデザイン Advent Calendar 2022 の8日目の記事です。

突然ですが、2022年10月にリリースされたPostgreSQL 15で新たにMERGE が追加されました。

https://www.postgresql.org/docs/15/sql-merge.html

このMERGE はSQL標準としてOracle, SQL Serverで採用されていましたが、これまでPostgreSQL, MySQLではサポートされていなかったので、自分としては触れる機会がありませんでした。

なので、今回はMERGE の基本的な使用方法について勉強した内容を記事にしていきたいと思います。

## 環境

```sql
postgres=# SELECT version();
                                                   version
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 on x86_64-pc-linux-musl, compiled by gcc (Alpine 11.2.1_git20220219) 11.2.1 20220219, 64-bit
```

## MERGE ってそもそも何？

MERGE はテーブルに対して条件付きでINSERT・UPDATE・DELETE を行うSQLです。
いわゆるUPSERT（更新データが存在するときは**UP**DATE, 存在しないときはIN**SERT**）処理を行うときに使用できます。

もともとPosgreSQLには`INSERT ON CONFLICT` という構文があり、INSERTするときにPrimary KeyやUnique Keyが重複する場合にUPDATEを行うという方式でUPSERTを実現していました。

https://www.postgresql.org/docs/15/sql-insert.html#SQL-ON-CONFLICT

`INSERT ON CONFLICT` があるならMERGE なくてもイイじゃん、そんなふうに思っていた時期が自分にもありました。

ただ、MERGE はUPDATE だけではなくDELETE も行える点やUPDATE する場合の条件などを細かく指定できるので、知っていると色々便利なのかなと思います。

## MERGE の構文

PostgreSQLの公式サイトからMERGE の構文を抜粋します。

```sql
[ WITH with_query [, ...] ]
MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source ON join_condition
when_clause [...]

where data_source is:

{ source_table_name | ( source_query ) } [ [ AS ] source_alias ]

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 } }

and merge_insert is:

INSERT [( column_name [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES }

and merge_update is:

UPDATE SET { column_name = { expression | DEFAULT } |
             ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]

and merge_delete is:

DELETE
```

うーん、難しい（笑）。
このあたりは実例を見たほうが理解しやすいと思いますので、安心してください。

:::note warn
余談ですが、`WITH`と`DO NOTHING`はPostgreSQLの拡張機能なので標準SQLにはありません。
他のRDBMSを触る際はご注意を。
:::

## MERGE を使ってみよう

では実際にMERGE を使ってみながら理解を深めていきましょう。

### 例1. UPDATE or INSERT（**data_source**がテーブルの場合）

まず例として次のようなケースを考えてみます。

- 商品の在庫数を管理する `item_stocks` テーブルと商品の在庫移動を管理する `stock_movements` テーブルがある
- `stock_movements` テーブルの中身を取得し、`item_stocks` テーブルに移動した商品の個数を加算する
- `item_stocks` テーブルにレコードがあればUPDATE して加算、レコードがなければINSERT する

テーブル定義は下記のようにします。
```sql
CREATE TABLE item_stocks (
  item_id INT PRIMARY KEY,
  quantity INT NOT NULL
);

CREATE TABLE stock_movements (
  id INT PRIMARY KEY,
  item_id INT NOT NULL,
  quantity INT NOT NULL
);
```

次に下記のようなテストデータを投入しておきます。

```sql
SELECT * FROM item_stocks;
 item_id | quantity
---------+----------
       1 |       10
       2 |       20

SELECT * FROM stock_movements;
 id | item_id | quantity
----+---------+----------
  1 |       1 |        5
  2 |       3 |       30
```

<details><summary>テストデータ投入用のSQLはこちら</summary>

```sql
INSERT INTO item_stocks VALUES
(1, 10),
(2, 20);

INSERT INTO stock_movements VALUES
(1, 1, 5),
(2, 3, 30);
```
</details>


ではMERGE を使用して、`stock_movements` テーブルの商品数を`item_stocks` テーブルに加算していきましょう。

```sql
MERGE INTO item_stocks i
USING stock_movements a ON i.item_id = a.item_id
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + a.quantity
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity)
  VALUES (a.item_id, a.quantity);
```

ちょっとだけ複雑ですが、細かく分解して見ていくと理解するのは難しくありません。
「よくわからんホイ」という方は下記を見ていただけたらと思います。

<details><summary>MERGE を分解して見ていこう</summary>


```sql
MERGE INTO item_stocks i
```

ここはこれからUPSERT を行う対象のテーブルを指定します。
特に難しくはないですね。

```sql
USING stock_movements a ON i.item_id = a.item_id
```

ここは`JOIN`をイメージするとわかりやすいと思います。
今回のデータでいうと下記のような感じです。

<table frame="box" border="1">
    <tr>
        <th colspan="2">item_stocks</th>
        <th rowspan="100">+</th>
        <th colspan="3">stock_movements</th> 
    </tr>
    <tr>
        <th>item_id</th><th>quantity</th>
        <th>id</th><th>item_id</th><th>quantity</th>
    </tr>
    <tr>
        <td>1</td><td>10</td>
        <td>1</td><td>1</td><td>5</td>
    </tr>
    <tr>
        <td>2</td><td>20</td>
        <td>-</td><td>-</td><td>-</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>2</td><td>3</td><td>30</td>
    </tr>
</table>

```sql
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + a.quantity
```

ここはCASE 文と似ていますね。

https://www.postgresql.org/docs/15/functions-conditional.html#FUNCTIONS-CASE

要は`item_stocks` テーブルにレコードが存在する（`MATCHED`）ときにこんな感じのUPDATE してねって書いてあるだけです。

```sql
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity)
  VALUES (a.item_id, a.quantity);
```

ここは`item_stocks` テーブルにレコードが存在しない（`NOT MATCHED`）ときにこんな感じのINSERT してねって書いてあります。

何となくイメージできたでしょうか？

</details>



MERGE を実行した結果、`item_stocks` テーブルのレコードは下記のように変更されています。

```sql
SELECT * FROM item_stocks ORDER BY item_id;
 item_id | quantity
---------+----------
       1 |       15
       2 |       20
       3 |       30
```

下記のようなイメージで処理が行われたということですね。
MERGE と言われると、たしかに納得の処理結果です。

<table frame="box" border="1">
    <tr>
        <th colspan="2">item_stocks（before）</th>
        <th rowspan="100">+</th>
        <th colspan="3">stock_movements</th>
        <th rowspan="100">→</th>
        <th colspan="2">item_stocks（after）</th> 
    </tr>
    <tr>
        <th>item_id</th><th>quantity</th>
        <th>id</th><th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
    </tr>
    <tr>
        <td>1</td><td>10</td>
        <td>1</td><td>1</td><td>5</td>
        <td>1</td><td>15</td>
    </tr>
    <tr>
        <td>2</td><td>20</td>
        <td>-</td><td>-</td><td>-</td>
        <td>2</td><td>20</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>2</td><td>3</td><td>30</td>
        <td>3</td><td>30</td>
    </tr>
</table>

`item_id=1` はレコードが既に存在するのでUPDATE, `item_id=3` はレコードが存在しなかったのでINSERTということで、見事 UPSERT が実現できています。

### 例2. UPDATE or INSERT（**data_source**がクエリの場合）

**data_source** （先程のSQLでいうと、`USING stock_movements a`）の部分はテーブルだけでなくクエリにしてもOKです。

先程のデータをもとに、`stock_movements` にもう少しレコードを追加してみましょう。

```sql
SELECT * FROM item_stocks;
 item_id | quantity
---------+----------
       1 |       10
       2 |       20

SELECT * FROM stock_movements;
 id | item_id | quantity
----+---------+----------
  1 |       1 |        5
  2 |       1 |       10
  3 |       3 |       30
  4 |       3 |       40
```

<details><summary>テストデータ投入用のSQLはこちら</summary>

```sql
TRUNCATE item_stocks;
INSERT INTO item_stocks VALUES
(1, 10),
(2, 20);

TRUNCATE stock_movements;
INSERT INTO stock_movements VALUES
(1, 1, 5),
(2, 1, 10),
(3, 3, 30),
(4, 3, 40);
```
</details>

同じ商品が複数回に分かれて在庫移動した、みたいなイメージですね。
商品ごとの在庫数を加算したいので、`GROUP BY` で`item_id`ごとに`quantity`を集計してみましょう。

```sql
SELECT item_id, SUM(quantity) AS quantity FROM stock_movements GROUP BY item_id ORDER BY item_id;
 item_id | quantity
---------+----------
       1 |       15
       3 |       70
```

このクエリを**data_source** としてMERGE を書いてみましょう（`ORDER BY` はいらないので取っちゃいます）。

```sql
MERGE INTO item_stocks i
USING (SELECT item_id, SUM(quantity) AS quantity FROM stock_movements GROUP BY item_id) a ON i.item_id = a.item_id
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + a.quantity
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity)
  VALUES (a.item_id, a.quantity);
```

先程`stock_movements` になっていたところがクエリになっているだけで、他は一緒ですね。

実行した結果は下記のようになります。

```sql
SELECT * FROM item_stocks ORDER BY item_id;
 item_id | quantity
---------+----------
       1 |       25
       2 |       20
       3 |       70
```

先ほどと同じく、レコードがある場合はUPDATE, ない場合はINSERTが行われているのがわかるかと思います。

<table frame="box" border="1">
    <tr>
        <th colspan="2">item_stocks（before）</th>
        <th rowspan="100">+</th>
        <th colspan="2">SELECT ... FROM stock_movements ...</th>
        <th rowspan="100">→</th>
        <th colspan="2">item_stocks（after）</th> 
    </tr>
    <tr>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
    </tr>
    <tr>
        <td>1</td><td>10</td>
        <td>1</td><td>15</td>
        <td>1</td><td>25</td>
    </tr>
    <tr>
        <td>2</td><td>20</td>
        <td>-</td><td>-</td>
        <td>2</td><td>20</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>3</td><td>70</td>
        <td>3</td><td>70</td>
    </tr>
</table>

### 例3. UPDATE or INSERT or DELETE

続いては、レコードが存在するときに条件付きでUPDATE するか DELETE するか切り替える方式も見ていきましょう。
これは `INSERT ON CONFLICT` にはない、MERGE 独特の機能だと思います。

先程の例に、次のような条件を加えてみましょう。

- 商品の在庫数を管理する `item_stocks` テーブルと商品の在庫移動を管理する `stock_movements` テーブルがある
- `stock_movements` テーブルの中身を取得し、`item_stocks` テーブルに移動した商品の個数を加算する
- `item_stocks` テーブルにレコードがあればUPDATE して加算、レコードがなければINSERT する
- <font color="red">**[New]**</font> `stock_movements.quantity` には負の値も入りうる（在庫が別の場所に移動して減ったとみなす）
- <font color="red">**[New]**</font> `item_stocks`テーブルにレコードが存在し、かつ在庫移動して`quantity`が 0 以下になった場合は DELETE する

文字が多くてややこしいですが、要は

- レコードがあって計算結果が `quantity <= 0` ... DELETE
- レコードがあって計算結果が `quantity > 0` ... UPDATE
- レコードない ... INSERT

ということです。

ということで、下記のようなテストデータを用意します。

```sql
SELECT * FROM item_stocks;
 item_id | quantity
---------+----------
       1 |       10
       2 |       20
       4 |       10

SELECT * FROM stock_movements;
 id | item_id | quantity
----+---------+----------
  1 |       1 |        5
  2 |       1 |       10
  3 |       3 |       30
  4 |       3 |       40
  5 |       4 |      -10
  6 |       5 |       -5
```

<details><summary>テストデータ投入用のSQLはこちら</summary>

```sql
TRUNCATE item_stocks;
INSERT INTO item_stocks VALUES
(1, 10),
(2, 20),
(4, 10);

TRUNCATE stock_movements;
INSERT INTO stock_movements VALUES
(1, 1, 5),
(2, 1, 10),
(3, 3, 30),
(4, 3, 40),
(5, 4, -10),
(6, 5, -5);
```
</details>

では、MERGE を書いていきましょう。

```sql
MERGE INTO item_stocks i
USING (SELECT item_id, SUM(quantity) AS quantity FROM stock_movements GROUP BY item_id) a ON i.item_id = a.item_id
WHEN MATCHED AND i.quantity + a.quantity <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + a.quantity
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity)
  VALUES (a.item_id, a.quantity);
```

`WHEN MATCHED i.quantity + a.quantity <= 0 AND THEN DELETE` の部分で、レコードがあり在庫数が0 以下なら削除、`WHEN MATCHED THEN UPDATE SET quantity = i.quantity + a.quantity` でそれ以外でレコードが存在するなら UPDATE となります。

:::note warn
WHEN が上から順に評価されて、マッチしたら抜けるのは CASE と一緒なので、UPDATEのところに `AND i.quantity + a.quantity > 0`とは書いていません。
:::

実行した結果は下記のようになります。

```sql
SELECT * FROM item_stocks ORDER BY item_id;
 item_id | quantity
---------+----------
       1 |       25
       2 |       20
       3 |       70
       5 |       -5
```

`item_id=4` のレコードが消えているのがおわかりでしょうか。
在庫移動の結果、`quantity=0` となったためDELETE されています。

<table frame="box" border="1">
    <tr>
        <th colspan="2">item_stocks（before）</th>
        <th rowspan="100">+</th>
        <th colspan="2">SELECT ... FROM stock_movements ...</th>
        <th rowspan="100">→</th>
        <th colspan="2">item_stocks（after）</th> 
    </tr>
    <tr>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
    </tr>
    <tr>
        <td>1</td><td>10</td>
        <td>1</td><td>15</td>
        <td>1</td><td>25</td>
    </tr>
    <tr>
        <td>2</td><td>20</td>
        <td>-</td><td>-</td>
        <td>2</td><td>20</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>3</td><td>70</td>
        <td>3</td><td>70</td>
    </tr>
    <tr>
        <td>4</td><td>10</td>
        <td>4</td><td>-10</td>
        <td colspan="2">DELETE</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>5</td><td>-5</td>
        <td>5</td><td>-5</td>
    </tr>
</table>

### 例4. DO NOTHING も使う

先程の例で、`item_id=5` のレコードが`quantity <=0` なのに結果にいることにお気付きの方もいたことでしょう。
~~君のような勘のいいガキは嫌いだよ。~~

これは、`item_stocks`テーブルにレコードが存在しなかったので、DELETE の条件である「レコードが存在すること」にマッチせずINSERT されてしまったことが原因です。
（`item_stocks`にレコードが無いのに、なんで在庫移動しとんねんというツッコミはやめてください＞＜）

ここで、「`item_stocks`テーブルにレコードが存在せず、かつ`stock_movements.quantity<=0` なら何もしない」ということを実現したいので、PostgreSQLの拡張機能である`DO NOTHING`を使用していきたいと思います。

使用するデータは先程と同様です。

```sql
SELECT * FROM item_stocks;
 item_id | quantity
---------+----------
       1 |       10
       2 |       20
       4 |       10

SELECT * FROM stock_movements;
 id | item_id | quantity
----+---------+----------
  1 |       1 |        5
  2 |       1 |       10
  3 |       3 |       30
  4 |       3 |       40
  5 |       4 |      -10
  6 |       5 |       -5
```

では、MERGE を書いていきましょう。

```sql
MERGE INTO item_stocks i
USING (SELECT item_id, SUM(quantity) AS quantity FROM stock_movements GROUP BY item_id) a ON i.item_id = a.item_id
WHEN MATCHED AND i.quantity + a.quantity <= 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET quantity = i.quantity + a.quantity
WHEN NOT MATCHED AND a.quantity <= 0 THEN
  DO NOTHING
WHEN NOT MATCHED THEN
  INSERT (item_id, quantity)
  VALUES (a.item_id, a.quantity);
```

長くなってきましたが、先程のSQLに`WHEN NOT MATCHED AND a.quantity <= 0 THEN DO NOTHING` が増えただけなのでご安心を。
結果は下記のようになります。

```sql
SELECT * FROM item_stocks ORDER BY item_id;
 item_id | quantity
---------+----------
       1 |       25
       2 |       20
       3 |       70
```

無事、在庫数が0以下のレコードが`item_stocks`に残らなくなりましたね。
めでたしめでたし。

<table frame="box" border="1">
    <tr>
        <th colspan="2">item_stocks（before）</th>
        <th rowspan="100">+</th>
        <th colspan="2">SELECT ... FROM stock_movements ...</th>
        <th rowspan="100">→</th>
        <th colspan="2">item_stocks（after）</th> 
    </tr>
    <tr>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
        <th>item_id</th><th>quantity</th>
    </tr>
    <tr>
        <td>1</td><td>10</td>
        <td>1</td><td>15</td>
        <td>1</td><td>25</td>
    </tr>
    <tr>
        <td>2</td><td>20</td>
        <td>-</td><td>-</td>
        <td>2</td><td>20</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>3</td><td>70</td>
        <td>3</td><td>70</td>
    </tr>
    <tr>
        <td>4</td><td>10</td>
        <td>4</td><td>-10</td>
        <td colspan="2">DELETE</td>
    </tr>
    <tr>
        <td>-</td><td>-</td>
        <td>5</td><td>-5</td>
        <td colspan="2">DO NOTHING</td>
    </tr>
</table>

## おしまい

という訳で、ちょっと長くなってしまいましたが、PostgreSQL 15で追加されたMERGE のご紹介でした。
最初に構文を見たときはグエーってなりましたが、使ってみるとそんなに怖い子ではなかったというのが正直な感想ですw。

明日は [yuki](https://twitter.com/yyykms123) さんの記事になります。
next系の記事ということなので、どんな内容を紹介してくれるのか自分も楽しみにしています。

## 参考URL

[MERGE (SQL) - Wikipedia](https://ja.wikipedia.org/wiki/MERGE_(SQL))
[PostgreSQL の INSERT ON CONFLICT と MERGE の簡易性能比較 - Qiita](https://qiita.com/fujii_masao/items/462bac9f6a107d6134c4)
[PostgreSQL 15にMERGE文UPSERTがやってくる | DevelopersIO](https://dev.classmethod.jp/articles/postgresql15-suppoerts-merge-syntax-upsert/)