PostgreSQLのUNIQUE NULLS NOT DISTINCT、NULLは同じ値として扱われる

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

前提として NULLS NOT DISTINCT はPostgreSQL 15から入った機能なので、それ以前のバージョンではそもそもこの話は起きない（NULLは常に区別される）。

あるテーブルで、こういうUNIQUE制約を張っていた。

UNIQUE NULLS NOT DISTINCT (category, code, deleted_at)

想定していたのはこう。

• deleted_at IS NULL（論理削除されていない）のときは (category, code) で一意
• deleted_at IS NOT NULL（論理削除済み）のときは (category, code, deleted_at) で一意（削除日時ごとに別レコード扱い）

何が起きたか

category = 'aaa' はそもそも code を持たない種別で、この値は常に NULL になる。

(category='aaa', code=NULL, deleted_at=NULL)
(category='aaa', code=NULL, deleted_at=NULL)

このパターンで2件目を登録しようとするとUNIQUE制約違反になった。

原因

通常のUNIQUE制約は NULL <> NULL として扱うので、NULLを含む行はいくら重複してもエラーにならない。ただし NULLS NOT DISTINCT（PostgreSQL 15以降）を付けると、そこだけ挙動が変わって NULL = NULL として扱われる。

なので code IS NULL の行同士は「同じ値」とみなされ、category が同じで deleted_at も同じ（＝NULL）なら重複扱いになってしまう。NULLS NOT DISTINCT は「削除済みレコードをdeleted_atの値ごとに区別したい」という目的で付けたものだったが、codeを持たないcategoryの存在は考慮していなかった。

対応

codeを持たないcategoryが存在する以上、テーブル全体に一つのUNIQUE制約をかける設計自体が無理があった。素直に「codeが存在するレコードだけ」を対象にした部分UNIQUEインデックスに切り替えた。これはPostgreSQL 15より前のバージョンでも使える方法。

CREATE UNIQUE INDEX items_unique_active
ON items (category, code)
WHERE deleted_at IS NULL
  AND code IS NOT NULL;

結果はこう。

codeを持たない種別は何件でも登録できて、codeを持つ種別は同じ値を二度登録できない。想定通りの挙動になった。

結論

NULLS NOT DISTINCT は便利だけど、「NULLをどう扱いたいか」を制約全体に対して一枚岩で決めることになる。カラムによってNULLの意味が違う（「値がない」なのか「codeという概念自体がない」なのか)場合は、素直にWHERE句付きの部分UNIQUEインデックスで対象を絞ったほうが意図がはっきりするし、バージョン依存もない。