LoginSignup
5
2

More than 1 year has passed since last update.

ミライトデザインAdvent Calendar 2022Day 8
@Nyokkiin株式会社ミライトデザイン

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

Last updated at Posted at 2022-12-08

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

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

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

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

環境

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（更新データが存在するときはUPDATE, 存在しないときはINSERT）処理を行うときに使用できます。

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

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

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

MERGE の構文

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

[ 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

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

余談ですが、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 する

テーブル定義は下記のようにします。

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
);

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

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
テストデータ投入用のSQLはこちら 
INSERT INTO item_stocks VALUES
(1, 10),
(2, 20);

INSERT INTO stock_movements VALUES
(1, 1, 5),
(2, 3, 30);

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

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);

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

MERGE を分解して見ていこう 
MERGE INTO item_stocks i

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

USING stock_movements a ON i.item_id = a.item_id

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

item_stocks + stock_movements
item_id quantity id item_id quantity
1 10 1 1 5
2 20 - - -
- - 2 3 30 
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 してねって書いてあるだけです。

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

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

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

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

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

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

item_stocks（before） + stock_movements → item_stocks（after）
item_id quantity id item_id quantity item_id quantity
1 10 1 1 5 1 15
2 20 - - - 2 20
- - 2 3 30 3 30

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 にもう少しレコードを追加してみましょう。

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
テストデータ投入用の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);

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

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 はいらないので取っちゃいます）。

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 になっていたところがクエリになっているだけで、他は一緒ですね。

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

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

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

item_stocks（before） + SELECT ... FROM stock_movements ... → item_stocks（after）
item_id quantity item_id quantity item_id quantity
1 10 1 15 1 25
2 20 - - 2 20
- - 3 70 3 70

例3. UPDATE or INSERT or DELETE

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

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

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

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

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

ということです。

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

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
テストデータ投入用の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);

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

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 となります。

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

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

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 されています。

item_stocks（before） + SELECT ... FROM stock_movements ... → item_stocks（after）
item_id quantity item_id quantity item_id quantity
1 10 1 15 1 25
2 20 - - 2 20
- - 3 70 3 70
4 10 4 -10 DELETE
- - 5 -5 5 -5

例4. DO NOTHING も使う

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

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

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

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

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 を書いていきましょう。

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 が増えただけなのでご安心を。
結果は下記のようになります。

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

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

item_stocks（before） + SELECT ... FROM stock_movements ... → item_stocks（after）
item_id quantity item_id quantity item_id quantity
1 10 1 15 1 25
2 20 - - 2 20
- - 3 70 3 70
4 10 4 -10 DELETE
- - 5 -5 DO NOTHING

おしまい

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

明日は yuki さんの記事になります。
next系の記事ということなので、どんな内容を紹介してくれるのか自分も楽しみにしています。

参考URL

MERGE (SQL) - Wikipedia
PostgreSQL の INSERT ON CONFLICT と MERGE の簡易性能比較 - Qiita
PostgreSQL 15にMERGE文UPSERTがやってくる | DevelopersIO

5
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
2