PostgreSQLで論理削除(NULL許容カラムがある)テーブルにユニーク制約を貼り、UPSERT(ON CONFLICT)する
対象環境
- PostgreSQL 9.5以上
- ON CONFLICTが対応しているもの
- Nullableなカラムを含めてユニーク制約を貼らなければならないテーブルがある
前提
- 正しくないアプローチだったらごめんなさい
- パフォーマンスに関しては未確認です
結論
ユニーク制約を貼る
ユニーク制約がないとUPSERTできないので、下記のようにCOALESCEを使ってNULL許容カラムにデフォルト値を入れてユニーク制約を貼ります。
※ COALESCEは第一引数がNULLの時、第二引数の値を返す。NULLじゃない時は第一引数の値を返す関数です。
|
1 2 |
create unique index unique_users_mail_address on "users" (mail_address, coalesce(deleted_at, '1900-01-01 00:00:00')); |
COALESCE使わないとPostgreSQLはNULL<>NULLなので、同じメールアドレスで何レコードもINSERT出来てしまいます。
そこで、COALESCEを使うことで論理削除テーブルにも正しくユニーク制約を貼れると思います。
UPSERTする
UPSERTする場合は、下記のようにCONFLICT句にもCOALESCEを適用してクエリを書きます。
|
1 2 3 4 5 6 7 8 9 10 |
insert into "users" (mail_address, "password") values ('test1@test.com', 'abc'), ('test2@test.com', 'abc') on conflict (mail_address, coalesce(deleted_at, '1900-01-01 00:00:00')) do update set "password" = EXCLUDED.password ; |
varcharやint型でも同様に''や0を使うことでNullableなカラムにユニーク制約が貼れそうです。
あとがき
- PostgreSQLは
NULL<>NULL
↑があるため、これまで自分は論理削除テーブルにはユニーク制約は貼らずに、アプリケーション側でユニークになるようにバリデーションをしてました。
ユニーク制約貼りたいのに貼れない方やUPSERTが出来なくて困っている方の役に立てたら嬉しいです。
