PostgreSQLでUPSERTを試してみる。

2020-02-01

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

構文

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

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

テストデータはこんな感じになります。
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

■同じタグの記事(最新5件)■同じタグの記事を見る