🐘
[PostgreSQL] EXCLUDED を使って UPSERT 文をちょっとだけ楽に書く
はじめに
PostgreSQL で UPSERT 文 が必要になり書き方を調べていたのですが、下記のような書き方が多くヒットしました。
-- ユニーク制約に違反したら何もしない
INSERT INTO テーブル名 (カラム1, カラム2,...)
VALUES (値1, 値2,...)
ON CONFLICT (ユニーク制約名)
DO NOTHING;
-- ユニーク制約に違反したら指定されたカラムを更新する
INSERT INTO テーブル名 (カラム1, カラム2,...)
VALUES (値1, 値2,...)
ON CONFLICT (ユニーク制約名)
DO UPDATE SET カラム1 = 値1, カラム2 = 値2, ...;
-- 例
INSERT INTO users (id, family_name, first_name, email, is_activated, is_admin)
VALUES ('user-id1', '山田', '太郎', 'example@example.com', TRUE, FALSE)
ON CONFLICT(id) DO UPDATE SET
family_name = '山田',
first_name = '太郎',
email = 'example@example.com',
is_activated = TRUE,
is_admin = FALSE;
DO UPDATE SET 以降で VALUES に指定した値と同じ値をわざわざ書き連ねて書くのが面倒くさく DRY でないのでもう少し良い書き方がないか調べました。
EXCLUDED を使う
DO UPDATE SET 以降で EXCLUDED という特別なテーブルが参照できるらしいです。このテーブルにはもともと INSERT されようとしていた値が入っているので、hoge='value' みたいにいちいち値を指定し直さなくて良くなります。
確認したドキュメントはこちら。
すべての行レベルの BEFORE INSERT トリガーの結果が excluded の値に反映されることに注意して下さい。 これらの結果として、行が挿入から除外されることになったかもしれないからです。
BEFORE INSERT トリガーが設定されている場合は、その処理を行った結果が EXCLUDED に入るようです。
先程例に出した INSERT 文を EXCLUDED を使うと以下のように書き換えられます。
INSERT INTO users (id, family_name, first_name, email, is_activated, is_admin)
VALUES ('user-id1', '山田', '太郎', 'example@example.com', TRUE, FALSE)
ON CONFLICT(id) DO UPDATE SET
family_name = EXCLUDED.family_name,
first_name = EXCLUDED.first_name,
email = EXCLUDED.email,
is_activated = EXCLUDED.is_activated,
is_admin = EXCLUDED.is_admin;
よく考えたら複数件 Upsert したいときは EXCLUDED を使わないと INSERT しようとした値で UPDATE が行えませんね。
INSERT INTO users (id, family_name, first_name, email, is_activated, is_admin)
VALUES
('user-id1', '山田', '太郎', 'example@example.com', TRUE, FALSE)
('user-id2', '田中', '太郎', 'example+tanaka@example.com', TRUE, FALSE),
('user-id3', '鈴木', '太郎', 'example+suzuki@example.com', TRUE, FALSE),
ON CONFLICT(id) DO UPDATE SET
family_name = EXCLUDED.family_name,
first_name = EXCLUDED.first_name,
email = EXCLUDED.email,
is_activated = EXCLUDED.is_activated,
is_admin = EXCLUDED.is_admin;
UPSERT (ON CONFLICT 句)、EXCLUDED はともに PostgreSQL 9.5 以降で利用可能なようです。
参考
Discussion