PostgreSQL 9.5の新機能CONFLICT（UPSERT）を使ってみた。

はじめに

UPSERTが採用されたという事で使用方法を調べてみました。
UPSERTとは
「このデータをINSERTしたいけど、同じデータが存在していて制約で弾かれるかもしれない。そうなったらUPDATEしたいなあ。」

と言った場合に使える機能です。
ただし、UPSERT句が作られた訳ではなく「一般的にUPSERTと言われている機能を搭載したよ」と言う事みたいですね。
この後は実際に制約を設定したテーブルにUPSERTを試してみてどうなるかを見ていきます。

検証環境
Mac OSX 10.9.5
PostgreSQL 9.5.1

構文

INSERT INTO テーブル名 VALUES ('値１', '値２', ...）
ON CONFLICT ON CONSTRAINT 制約名
DO UPDATE SET カラム１='値', カラム２='値', ...;

INSERTとUPDATEでON CONFLICT ON CONSTRAINTを挟まれています。
INSERTが成功した場合と、UPDATEに移行した場合で値を変更できるのが良いですね。

UPSERTを試す

テーブルを用意

CREATE TABLE upst 
(id SERIAL
,title VARCHAR(20)
,CONSTRAINT upst_pkey PRIMARY KEY(id));

４行目でプライマリキーの制約名をCONSTRAINTでupst_pkeyと名付けています。
制約名は設定しなくても自動生成されますが調べないといけません。

制約名を調べる場合

SELECT table_name, constraint_name, constraint_type
FROM   information_schema.table_constraints
WHERE  table_name='upst';

 table_name |    constraint_name    | constraint_type 
------------+-----------------------+-----------------
 upst       | upst_pkey             | PRIMARY KEY
 upst       | 2200_16525_1_not_null | CHECK

実行

INSERT INTO upst VALUES (1,'INST')
ON CONFLICT ON CONSTRAINT upst_pkey
DO UPDATE SET title='UPDT';

１行目は普通のINSERT文。
２行目は制約名を設定。
３行目はINSERTが実行できなかった場合のUPDATE文。

結果

 id  | title 
----+-------
  1 | INST

制約される値がないのでINSERTが成功しました。
それでは、もう一度同じSQL文を実行してみます。

 id  | title 
----+-------
  1 | UPDT

カラム”id”には”１”が存在するため制約でINSERTが弾かれてUPDATEが実行されました。

DO NOTHINGで試す

DO UPDATEの代わりにDO NOTHINGを使用する事ができます。
使用すると制約違反が発生してもエラーが発生しません。
試してみます。

テーブルを用意

--drop table upst_con;
CREATE TABLE upst_con
 (id int,
  title varchar(10),
  CONSTRAINT id_ukey UNIQUE(id));
INSERT INTO upst_con VALUES (1,'inst');

 id | title 
----+-------
  1 | inst

今度はユニークキーで設定しました。

実行

INSERT INTO upst_con VALUES (1,'inst_1'), (2,'inst_2')
ON CONFLICT 
DO NOTHING;

制約を複数設定していてもON CONSTRAINTで制約名の指定をしない場合は全ての制約が含まれるようです。
１つだけ指定した場合はその１つだけが制約に適用されます。

結果

 id | title  
----+--------
  1 | inst
  2 | inst_2

最初の (1,'inst_1') は id が重複するので処理が行われません。
しかしエラーは発生していないので処理は進み、次の（2,'inst_2') は重複が無いのでINSERT処理がされています。

さいごに

制約がユニークキーの場合、NULLをINSERTしようとすると重複していても実行できてしまうので注意が必要です。
UPSERT機能自体は、INSERT文を実行してみてダメだったらUPDATE文を実行するという２つのSQL文で行っていた流れを１つにまとめられるのでとても便利だと思いました。