PostgreSQLでUPSERTを試してみる。

いつの間にか（4年前のPostgreSQL9.5）PostgreSQLにUPSERT機能が追加されていました。
UPSERT=登録するデータが重複していたら、更新に切り替える。

• 目次
• 構文
• テスト用テーブルを作成
• UPSERTを試してみる（なければ登録、あれば更新）
• DO NOTHINGを試してみる（なければ登録、あれば何もしない）

構文

insert into テーブル名 values ('カラム１', 'カラム２', ...）
on conflict (ユニークキー)
do update set カラム１='値', カラム２='値', ...;

CONSTRAINTに指定していしているユニークキーをもとにデータが重複しているか判定を行い、登録または更新を実施する。
判定を行うカラムにはユニーク制約をつける必要があります。

node.テスト用テーブルを作成

テスト用にこんな感じのテーブルとデータを入れておく

--テーブル作成
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, '青山');

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

UPSERTを試してみる（なければ登録、あれば更新）

• UPSERTを使わない場合はエラーとなることを確認

--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='池田';

  SQL実行後、池田に更新されていることを確認。
  

• 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='宇井';

  SQL実行後、宇井に更新されていることを確認。
  

• 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='榎本';

  SQL実行後、榎本に更新されていることを確認。

• 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 NOTHINGを試してみる（なければ登録、あれば何もしない）

構文は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がかぶっている北原は登録されていないことを確認