エラーの内容
UNIQUE 制約を追加するマイグレーションを実行すると、PostgreSQL が突然止まります：
ERROR: could not create unique index "users_email_key"
DETAIL: Key (email)=(user@example.com) is duplicated.
PostgreSQL はインデックスを構築する前にテーブル全体をスキャンします。同じ値を持つ2行を見つけた瞬間に処理を中断し、制約は一切作成されません。
主に以下の3つの状況が原因となります：
- マイグレーションで、以前は制約のなかったカラムに
UNIQUEを追加しようとした - 複数のデータソースからデータをマージする際に、一意性の確認を行わなかった
- アプリケーションコードのバグにより、ガードが設置される前に重複した挿入が許可されていた
まず重複を確認する
まだ何も変更しないでください。最初に状況を正確に把握しましょう：
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
これにより、重複しているすべてのメールアドレスとその件数が表示されます。数百万行のテーブルの場合は、クリーンアップ戦略を決める前に規模を把握するために LIMIT 100 を追加してください。
問題のある値だけでなく、行全体を確認するにはウィンドウ関数を使います：
SELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) ranked
WHERE rn > 1;
rn > 1 の行が重複です。rn = 1 の行が残すべきレコードです。
簡単な修正：重複行を削除する
重複を安全に破棄できる場合は、id が最も小さい行を残してその他を削除します：
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) ranked
WHERE rn > 1
);
永続化する前に結果を確認できるよう、トランザクション内でラップします：
BEGIN;
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) ranked
WHERE rn > 1
);
-- クリーンアップが成功していれば0行が返るはず
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- 問題なければ COMMIT、おかしければ ROLLBACK。
COMMIT;
実際の users テーブルで500,000行、数百件の重複がある場合、通常この削除は1秒以内に完了します。数百万件の重複を持つ大規模テーブルでは、テーブルのロックが長時間にわたらないよう、バッチ削除が必要になる場合があります。
削除できない場合 — マージまたは NULL 化で対処する
削除が常に選択肢とは限りません。他のテーブルが外部キーで重複行を参照していたり、ビジネスルール上すべてのレコードを保持する必要がある場合もあります。
方法A：外部キーの参照先を変更してから削除する
-- 重複ユーザー（id=99）の注文をすべて正規のユーザー（id=42）に移動する
UPDATE orders SET user_id = 42 WHERE user_id = 99;
-- これで重複を安全に削除できる
DELETE FROM users WHERE id = 99;
方法B：重複する値を NULL 化する
PostgreSQL のユニークインデックスは NULL 値を完全に無視します。同じカラムに複数の NULL があっても競合しません。そのため、カラムが NULL を許可している場合、行を削除せずに重複値を空にすることができます：
UPDATE users
SET email = NULL
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) ranked
WHERE rn > 1
);
制約を追加する
テーブルがきれいになりました。制約を作成しましょう：
-- 推奨：後で削除または参照できる名前付き制約
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
-- 代替：スタンドアロンインデックス
CREATE UNIQUE INDEX users_email_key ON users (email);
大規模な本番テーブルの場合は CONCURRENTLY を使用してください。これを使わないと、PostgreSQL はインデックス構築中に AccessShareLock を保持し、たとえば5,000万行のテーブルでかかる時間の間、書き込みがブロックされます。
CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
注意点として、CONCURRENTLY はトランザクションブロック内では実行できません。スタンドアロンのステートメントとして実行してください。
マイグレーションで再発を防ぐ
このエラーがマイグレーションスクリプト内で発生した場合は、マイグレーションが実行される前にデータがきれいであることを前提とせず、重複排除のステップをマイグレーション内に直接組み込みましょう：
-- Migration: 20240501_add_unique_email.sql
-- Step 1: Remove duplicates
DELETE FROM users
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM users
) ranked
WHERE rn > 1
);
-- Step 2: Add the constraint
ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
両方のステップをまとめることで、データがどのような状態であっても対応できるマイグレーションになります。次の環境での予期しない問題がなくなります。
修正を確認する
-- 1. Confirm no duplicates remain
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Expected: 0 rows
-- 2. Confirm the constraint exists in the catalog
SELECT conname, contype
FROM pg_constraint
WHERE conrelid = 'users'::regclass
AND conname = 'users_email_key';
-- Expected: 1 row, contype = 'u'
-- 3. Confirm the constraint actually rejects duplicates
INSERT INTO users (email) VALUES ('user@example.com');
INSERT INTO users (email) VALUES ('user@example.com'); -- must fail
-- Expected: ERROR: duplicate key value violates unique constraint