はじめに
データベース操作していると、データ追加しようとしたときに主キーがすでに存在した場合にはデータの更新に切り替えたいってとき、ありますよね。
そういうのを「upsert」って呼んでて、例えばMySQL 5.6では「13.2.5.3 INSERT ... ON DUPLICATE KEY UPDATE 構文」として用意されていて便利そうだなって思っておりましたが、調べてみるとPostgreSQLにも類似の機能があることに気づきましたので、メモしておきます。
INSERT ～ ON CONFLICT 構文
「INSERT」に拠ると、
ON CONFLICT [ conflict_target ] conflict_action
ここでconflict_targetは以下のいずれかです。
( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] ON CONSTRAINT constraint_nameまたconflict_actionは以下のいずれかです。
DO NOTHING DO UPDATE SET { column_name = { expression | DEFAULT } | ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) | ( column_name [, ...] ) = ( sub-SELECT ) } [, ...] [ WHERE condition ]
少々難しく見えますが、割りと融通を利かせた仕組みであることがわかると思います。
conflict_target : 競合したとみなす条件
ON CONSTRAINT constraint_name の方はわかりやすいですよね、主キーやユニークキー、NOT NULL、CHECKなどカラムに設けた「制約」につけた名称を指定するときに使います。
もう一方の表現は、この場で条件をカラム名やカラム名を伴った式で表現するものになります。
単にカラム名を列挙する、式インデックスとして定義できる式で記述するということになります。
CREATE INDEXで与えられる式が使えるようです。
conflict_action : 競合した時どうするか
DO NOTHING は、競合しても何もしない、行の挿入をしないというものです。
DO UPDATE は代替の動作として、挿入されようとしていた行と競合する既存の行を更新するものです。 この動作が、いわゆる'upsert'になります。
例（ドキュメントからピックアップ）
ドキュメントから ON CONFLICTに関係するサンプルをピックアップします。
INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
didが競合した時、更新する動作ですが、《元々挿入されようとしていた値を参照するために、特別なEXCLUDEDテーブルが使用されている》ところが注目です。
-- 特定の郵便番号については既存の販売店を更新しません
INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution')
ON CONFLICT (did) DO UPDATE
SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')'
WHERE d.zipcode <> '21201';
この例では、挿入されようとしていた値を参照するためのEXCLUDEDと、現在の値を参照するdistributorsのエイリアスdが使われているところに注目です。 新しいdnameと従来のdnameを文字列連結で合成して更新しているわけです。
また、WHERE句により、特定のzipcode以外のときにのみ更新する動作になっているところも注目です。
さいごに
ON CONFLICT句のおかげで柔軟な競合時の動作を指定できるのは便利ですね。 実際に使ってみたいものです。