PostgreSQLでMERGEを試してみる。

PostgreSQLでMERGEを試してみる。

細かく書くと何しているかわからなくなりそう

2023-05-30PostgreSQL

Postgresql15からMERGE文が実装されました。
更新データと登録対象テーブルのデータをもとにINSERT、UPDATE、DELETEができます。
UPSERT(登録するデータが重複していたら、更新に切り替える)もMERGE文で可能です。

公式サイト

conflictを使用したUPSERTは こちら

構文

MERGE INTO 更新対象のテーブル USING 登録したデータ
ON 条件
WHEN MATCHED THEN 条件に一致したときの動作(UPDATE/DELETE)またはDO NOTHING(何もしない)
WHEN NOT MATCHED THEN 条件に一致しなかったときのINSERT

テスト用テーブルを作成

--テーブル作成
CREATE TABLE members (
	id serial NOT NULL,                 --主キー
	class varchar(16) NOT NULL,          --クラス名
	no smallint,                        --出席番号
	name varchar(16) NOT NULL,          --名前
	delete_flg boolean  default false,   --削除フラグ

	constraint users_pkey primary key (id),     --主キー設定
	constraint users_unique unique (class, no)  --クラス名、出席番号にユニーク制約をつける
);

--テストデータ登録
insert into  members (class, no, name) values ('A', 1, '青木');
insert into  members (class, no, name) values ('A', 2, '青山');

テストデータはこんな感じになります。

postgresql_1.png

MERGEでUPSERT を試してみる(なければ登録、あれば更新)

  • UPSERT を使わない場合はエラーとなることを確認
--ユニーク制約が重複したINSERT
insert into  members (class, no, name) values ('A', 2, '池田');

postgresql_2.png

  • UPSERT を実行する
--更新データを2つ指定してエイリアスaddをつける
merge into members using (values ('A', 2, '池田'),('A', 3, '宇井')) as add (class, no, name)
on members.class = add.class and members.no = add.no --条件にclassとnoが一致することを指定
when matched then
	update set name = add.name -- 条件に一致した場合UPDATEを記述(nmaeを更新)
when not matched then
	insert (class, no, name) values (add.class, add.no, add.name); --条件に一致しない場合INSERTを記述

実行後、class:A、no:2は存在していたので池田に更新されている、
class:A、no:3は存在しなかったので宇井が新規登録されていることを確認。

postgresql_3.png

追加で条件を足してみる

when matchedの後に追加で条件を追加することができます。

merge into members using (values ('A', 2, '池田'),('A', 3, '大森'),('A', 4, '柿崎')) as add (class, no, name)
on members.class = add.class and members.no = add.no --条件にclassとnoが一致することを指定
when matched and members.delete_flg = false then -- and を追記し、条件を追加する
	update set name = add.name -- class、noが一致してdelete_flg = falseの時更新
when matched and members.delete_flg = true then
	delete -- class、noが一致してdelete_flg = trueeの時は削除
when not matched then
	insert (class, no, name) values (add.class, add.no, add.name); --条件に一致しない場合INSERTを記述

実行前にclass:A、no:2の池田をdelete_flg = trueにしておく

postgresql_4.png

実行後

postgresql_5.png

class:A、no:2の池田はdelete_flg = trueだったので削除、
class:A、no:3の宇井はdelete_flg = falseだったので大森に更新、
class:A、no:4の柿崎は新規登録されていることを確認。

別のテーブルのデータで更新する。

別の更新用テーブルを作成


-- maint列の内容により操作を切り替えるテーブル 
CREATE TABLE maintenance(
	id serial NOT NULL,                 --主キー
	maint varchar(1) not null, -- 操作内容(D:削除、U:更新、N:新規)
	class varchar(16) NOT NULL,          --クラス名
	no smallint,                        --出席番号
	name varchar(16) NOT NULL,          --名前
	memo text --メモ
);

insert into  maintenance (maint, class, no, name, memo) values ('D', 'A', 1, '青木', 'A-1は存在してmaintが「D」なので削除されるはず');
insert into  maintenance (maint, class, no, name, memo) values ('U', 'A', 2, '青山(更新)', 'A-2は存在して「U」なのでnameで更新されるはず');
insert into  maintenance (maint, class, no, name, memo) values ('U', 'B', 2, '青山(更新B)', 'B-2は存在しないけど「U」なので何も起きないはず');
insert into  maintenance (maint, class, no, name, memo) values ('N', 'A', 2, '青山(更新C)', 'A-2は存在するけど「N」なので何も起きないはず');
insert into  maintenance (maint, class, no, name, memo) values ('N', 'A', 8, '桐下', 'A-8は存在せず、「N」なので新規登録されるはず');

postgresql_6.png

maintenanceを参照し、maintの値によって処理を切り替えるmerge文を作成

merge into members using maintenance -- usingにテーブル名を指定
on members.class = maintenance.class and members.no = maintenance.no -- 条件はmaintenanceのclass、noの一致を指定(条件①)
when matched and maintenance.maint = 'U' then -- 条件①に一致しmaint = 'U'の時更新
	update set name = maintenance.name
when matched and maintenance.maint = 'D' then -- 条件①に一致しmaint = 'D'の時削除
	delete
when not matched and maintenance.maint = 'N' then insert  -- 条件①に一致せず、maint = 'D'の時更新
	(class, no, name) values (maintenance.class, maintenance.no, maintenance.name)
when not matched then -- 全ての条件に一致しない場合は何もしない(※何もしないので記述しなくてもOK)
	DO NOTHING;

実行前のmembersテーブル
postgresql_7.png

実行後

postgresql_8.png

期待通りに更新されていることを確認。