いつの間にか(4年前のPostgreSQL9.5)PostgreSQLにUPSERT機能が追加されていました。
UPSERT=登録するデータが重複していたら、更新に切り替える。
insert into テーブル名 values ('カラム1', 'カラム2', ...)
on conflict (ユニークキー)
do update set カラム1='値', カラム2='値', ...;
CONSTRAINTに指定していしているユニークキーをもとにデータが重複しているか判定を行い、登録または更新を実施する。
判定を行うカラムにはユニーク制約をつける必要があります。
テスト用にこんな感じのテーブルとデータを入れておく
--テーブル作成
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 (id, class, no, name) values (1, 'A', 1, '青木');
insert into members (id, class, no, name) values (2, 'A', 2, '青山');
--idが重複したINSERT
insert into members (id, class, no, name) values (2, 'A', 3, '池田');
UPSERTを実行する
--id=2のデータを登録する、データがある場合はnameを池田で更新する。
insert into members (id, class, no, name) values (2, 'A', 3, '池田')
on conflict (id)
do update set name='池田';
class, noで判定する
--class=A, no=2のデータを登録する、データがある場合はnameを宇井で更新する。
insert into members (id, class, no, name) values (3, 'A', 2, '宇井')
on conflict (class, no) --ここがidのままだとエラーになる
do update set name='宇井';
on conflictの後にユニーク制約名を指定する方法もある。
insert into members (id, class, no, name) values (3, 'A', 2, '榎本')
on conflict on constraint members_unique --class, noのユニーク制約名を指定
do update set name='榎本';
on conflictにユニーク成約でないカラムを指定するとエラーになります。
insert into members (id, class, no, name, delete_flg) values (3, 'A', 2, '大河内', false)
on conflict (class, no, delete_flg)--delete_flgも追加してみる
do update set name='大河内';
構文はDO UPDATEの部分をDO NOTHINGに変更する。
--class, noが重複していたら何もしない
insert into members (id, class, no, name, delete_flg) values (3, 'A', 3, '柿崎', false)
on conflict (class, no)
do nothing;
insert into members (id, class, no, name, delete_flg) values (4, 'A', 3, '北原', false)
on conflict (class, no)
do nothing;
柿崎は登録されるけど、class, noがかぶっている北原は登録されていないことを確認