もろもログ

ちょっとした技術メモを忘れないうちに書いていく

PostgreSQLでUPSERTを試してみる。

2020-02-01

もっと早く知っていれば、、、

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

※Postgresql15からMERGE文でもUPSERTができるようになりました。
方法は こちら

構文

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

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, '青山');

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

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

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

postgresql_2.png

  • UPSERT を実行する
--id=2のデータを登録する、データがある場合はnameを池田で更新する。
insert into  members (id, class, no, name) values (2, 'A', 3, '池田')
on conflict (id)
do update set name='池田';

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

  • 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 実行後、宇井に更新されていることを確認。 postgresql_4.png

  • 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 実行後、榎本に更新されていることを確認。 postgresql_5.png

  • 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='大河内';

postgresql_6.png

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

postgresql_7.png

目次