LoginSignup
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@nuko_yokohama(nuko yokohama)

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

Posted at

はじめに

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

MERGE文

MERGE文は「テーブルの行を条件付きでINSERT、UPDATE、DELETEする」（PostgreSQL 15文書のMERGE文の説明文から引用）
Oracle等の商用DBMSでは以前から実装されているSQL文だったが、PostgreSQLでの歴史は新しく、PostgreSQL 15から実装されている。

MERGE文の例

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

簡単な例でMERGE文の動作を示す。
以下のような2つのテーブル(source, 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)

以下はsoueceとtargetのidがマッチしないsourceのレコードをtargetに挿入する例である。

$ 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の値に更新する。

$ 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(2023-07～2024-03)をざっと見た感じでは、PostgreSQL 17ではMERGE文に対して以下の3つの改善が入るようだ。

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

検証バージョン

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

1.更新可能ビューに対するMERGE

Commitfestページ：MERGE INTO updatable_view

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

例えば、先程の例で挙げたtargetに対して単にORDER BYをかけるシンプルなビューtarget_vを作成する。

\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での実行例

\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では更新可能なビューに対する操作は成功する。

\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

PostgreSQL 16までのMERGE文のWHEN句の構文は以下のようになっていた。

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）

{ 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と同じ挙動になる。

-- 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を実行した場合には、以下のようになる。

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

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

PostgreSQL 16までのMERGE文の構文

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

PostgreSQL 17のMERGE文の構文

[ 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指定を追加して実行した例を示す。

-- 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への移行がまた少し楽になるのかもしれない。

3
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
3
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?