PostgreSQL『duplicate null key value violates unique constraint』の原因と対処

ERROR:  duplicate key value violates unique constraint "uq_users_email"
DETAIL:  Key (email)=(NULL) already exists.

-- あるいは、式/部分ユニーク由来
ERROR:  duplicate key value violates unique constraint "uq_users_email_norm"
DETAIL:  Key (lower(btrim(email)))=(null) already exists.

□ UNIQUE ... NULLS NOT DISTINCT（PostgreSQL 15+）でNULLを同一視
□ 部分ユニーク：WHERE email IS NULL などで「NULLは1行だけ」を保証
□ 式ユニーク：coalesce(email,'')、lower(btrim(email)) 等でNULL→同一値に正規化
□ 複合ユニーク＋NULLS NOT DISTINCT（複数列の全NULLが衝突）
□ INSERT ... ON CONFLICT / UPSERT が同一「NULL相当」キーを作ろうとしている

-- 失敗した制約/インデックス名をメッセージから控える（例：uq_users_email）

-- 1) インデックス/制約の定義を取得
SELECT i.relname AS index_name,
       pg_get_indexdef(i.oid) AS indexdef,
       ix.indisunique,
       ix.indnullsnotdistinct         -- trueなら「NULLを同一視」
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
WHERE i.relname = 'uq_users_email';

-- 2) 部分ユニークか（WHERE句が付いていないか）
SELECT pg_get_indexdef(indexrelid)
FROM pg_index
WHERE indexrelid = 'uq_users_email'::regclass;

-- 3) ビューで列定義と重複データを確認（例：emailがNULLの件数）
SELECT COUNT(*) FILTER (WHERE email IS NULL) AS null_rows,
       COUNT(*) FILTER (WHERE email IS NOT NULL) AS non_null_rows
FROM users;

-- 4) 式インデックスなら、式を再現して重複を探す
-- 例：lower(btrim(email)) がキーなら
SELECT lower(btrim(email)) AS k, COUNT(*)
FROM users
GROUP BY 1 HAVING COUNT(*) > 1;

-- NULLを同一視するユニーク制約
CREATE TABLE users(id bigserial PRIMARY KEY, email text);
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE NULLS NOT DISTINCT (email);

INSERT INTO users(email) VALUES (NULL);  -- OK
INSERT INTO users(email) VALUES (NULL);  -- NG：duplicate ... (email)=(NULL)

CREATE UNIQUE INDEX uq_users_email_null_one
  ON users ((1)) WHERE email IS NULL;  -- email IS NULL 行は常にキー1で衝突
INSERT INTO users(email) VALUES (NULL);  -- OK
INSERT INTO users(email) VALUES (NULL);  -- NG：duplicate ... uq_users_email_null_one

-- 空文字とNULLを同一視（coalesce）＋前後空白を除去＋小文字化
CREATE UNIQUE INDEX uq_users_email_norm
  ON users (lower(btrim(coalesce(email, ''))));

INSERT INTO users(email) VALUES (NULL);      -- OK
INSERT INTO users(email) VALUES (''   );      -- NG（NULLと同一視）
INSERT INTO users(email) VALUES ('  ' );      -- NG（btrimで空→同一）

( A ) 複数NULLを許可したい（既定のUNIQUEに戻す）
( B ) 「NULLは1件まで」を維持したい（正しいユニーク定義に集約）
( C ) NULLと空文字を厳密に区別/正規化したい（式を修正、CHECKで入口統制）
( D ) 同一視ルールを広げたい/狭めたい（lower/btrim/coalesce の再設計）

-- 1) 競合回避のため、新定義のユニークを作成（オンライン切替）
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email_distinct
  ON users (email) NULLS DISTINCT;   -- 15+ は明示可（既定はNULLS DISTINCT）

-- 2) 旧インデックス/制約をドロップして差し替え
DROP INDEX CONCURRENTLY uq_users_email;           -- 旧名（存在する方）
ALTER INDEX uq_users_email_distinct RENAME TO uq_users_email;

-- ※ 既存データに「式ユニーク」など別由来の衝突がある場合は、先に式をやめる/クリーニングする

-- 1) 15+：UNIQUE NULLS NOT DISTINCT を使う（推奨・明快）
ALTER TABLE users
  ADD CONSTRAINT uq_users_email UNIQUE NULLS NOT DISTINCT (email);

-- 2) 旧来：部分ユニークで厳密化（NULLのときだけ衝突）
DROP INDEX IF EXISTS uq_users_email_null_one;
CREATE UNIQUE INDEX uq_users_email_null_one
  ON users ((1)) WHERE email IS NULL;

-- 悪い例（NULLを''に潰すと衝突を誘発）
-- CREATE UNIQUE INDEX uq_users_email_norm ON users (lower(btrim(coalesce(email,''))));

-- 改善：NULLはNULLのまま、空白除去と小文字化だけを同一視
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email_norm2
  ON users (lower(btrim(email)));

-- 入口で空文字をNULLにしない/するかはポリシーで統一（CHECKやトリガで正規化）
ALTER TABLE users
  ADD CONSTRAINT email_not_empty CHECK (email IS NULL OR btrim(email) <> '');

-- NULL同一視のユニークを前提に、衝突時は既存行を更新する
INSERT INTO users(email, name)
VALUES (NULL, 'first')
ON CONFLICT ON CONSTRAINT uq_users_email  -- NULLS NOT DISTINCT の制約
DO UPDATE SET name = EXCLUDED.name;

-- 部分ユニークと併用するなら「衝突対象」を正しく指す
INSERT INTO users(email, name)
VALUES (NULL, 'first')
ON CONFLICT ON CONSTRAINT uq_users_email_null_one
DO UPDATE SET name = EXCLUDED.name;

-- 1) 「NULLを同一視」した場合に衝突するグループを特定
SELECT 'email_is_null' AS key, COUNT(*) AS cnt
FROM users
WHERE email IS NULL
GROUP BY 1 HAVING COUNT(*) > 1;

-- 2) 式ユニーク（lower(btrim(email))) の重複候補
SELECT lower(btrim(email)) AS k, COUNT(*)
FROM users
GROUP BY 1 HAVING COUNT(*) > 1;

-- 3) 修復例：NULL重複のうち“古い方”をアーカイブへ退避
WITH d AS (
  SELECT id, ROW_NUMBER() OVER (ORDER BY id) AS rn
  FROM users WHERE email IS NULL
)
DELETE FROM users
WHERE id IN (SELECT id FROM d WHERE rn > 1);

-- 例：式ユニーク(coalesce) → 部分ユニーク（NULL1件）へ置換

-- 0) 重複現存の有無を確認し、必要ならアーカイブ/正規化
-- 1) 新インデックスを並行作成
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email_null_one
  ON users ((1)) WHERE email IS NULL;

-- 2) アプリを短時間停止 ≒ 変更ウィンドウ（衝突するとDDLが止まるため）
-- 3) 旧インデックスを削除
DROP INDEX CONCURRENTLY uq_users_email_norm;
-- 4) 必要なら名称を統一
--    （または制約として付け直す）

-- デフォルト：どれか1列がNULLなら「異なる」と見なされ、複数可
ALTER TABLE sessions ADD CONSTRAINT uq_user_device UNIQUE (user_id, device_id);

-- 15+で「両方NULLも衝突」にしたい場合
ALTER TABLE sessions
  ADD CONSTRAINT uq_user_device_nd UNIQUE NULLS NOT DISTINCT (user_id, device_id);

□ どのユニーク定義がエラー源か（制約/インデックス名・pg_get_indexdef）
□ NULLS NOT DISTINCT / 部分ユニーク / 式ユニークのどれか
□ 仕様：NULLは何件許容？ 空文字や空白は同一視？（チームで合意）
□ 必要なら部分ユニーク or NULLS NOT DISTINCT へ集約し、式のcoalesceは避ける
□ ON CONFLICT で衝突先制約を明示
□ 変更は CREATE INDEX CONCURRENTLY → DROP CONCURRENTLY の順でオンライン化

-- 準備
DROP TABLE IF EXISTS users;
CREATE TABLE users(id bigserial PRIMARY KEY, email text, name text);

-- 1) NULLを同一視するユニーク（エラー再現）
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE NULLS NOT DISTINCT (email);
INSERT INTO users(email) VALUES (NULL);  -- OK
INSERT INTO users(email) VALUES (NULL);  -- NG

-- 解消（複数NULLを許可したい）
ALTER TABLE users DROP CONSTRAINT uq_email;
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);  -- 既定（NULLは区別）

-- 2) 部分ユニークでNULL1件（再現）
CREATE UNIQUE INDEX uq_email_null_one ON users((1)) WHERE email IS NULL;
INSERT INTO users(email) VALUES (NULL);  -- OK
INSERT INTO users(email) VALUES (NULL);  -- NG

-- 3) 式ユニークの落とし穴（NULL=空文字=空白）
CREATE UNIQUE INDEX uq_email_norm ON users(lower(btrim(coalesce(email,''))));
INSERT INTO users(email) VALUES (NULL);   -- OK
INSERT INTO users(email) VALUES ('');     -- NG（NULL相当で衝突）
-- → 改善：coalesceを外す
DROP INDEX uq_email_norm;
CREATE UNIQUE INDEX uq_email_norm2 ON users(lower(btrim(email)));