まえがき
コメントの@cryksさんが記載してくれているとおり、unique indexにwhere句を利用するほうが2万倍良さそうです。
以下は参照しないでください…。
対象環境
- PostgreSQL 9.5以上
- ON CONFLICTが対応しているもの
- Nullableなカラムを含めてユニーク制約を貼らなければならないテーブルがある
前提
- 正しくないアプローチだったらごめんなさい
- パフォーマンスに関しては未確認です
結論
ユニーク制約を貼る
ユニーク制約がないとUPSERTできないので、下記のようにCOALESCEを使ってNULL許容カラムにデフォルト値を入れてユニーク制約を貼ります。
※ COALESCEは第一引数がNULLの時、第二引数の値を返す。NULLじゃない時は第一引数の値を返す関数です。
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を適用してクエリを書きます。
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が出来なくて困っている方の役に立てたら嬉しいです。