ユーザー名を変更・退会できるサービスでは、データを物理的に削除せず、削除日時を設定する場合があります。これは**論理削除（ソフトデリート）**と呼ばれる設計です。
たとえば、次の要件があるとします。
- 利用中のユーザー同士で同じユーザー名を登録できない
- 退会済みのユーザー名は再利用できる
- 2人が同時に登録しても、同じユーザー名を持つ現役ユーザーを作らない
PostgreSQLの部分UNIQUEインデックスなら、こうしたルールをアプリ側の事前チェックだけに依存せず、データベースで保証できます。
普通のUNIQUE制約ではなぜ困る？
CREATE TABLE app_users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
handle TEXT NOT NULL,
deleted_at TIMESTAMPTZ,
UNIQUE (handle)
);
この定義は、退会済みの行にも一意性を要求します。退会後の再登録を許可したい要件には合いません。
では UNIQUE(handle, deleted_at) にすればよいのでしょうか。これも違います。通常のPostgreSQLのUNIQUEでは、NULL同士は異なる値として扱われます。deleted_at がNULLの現役行を複数作れてしまうため、肝心の重複防止になりません。
解決策：現役ユーザーだけを対象にする
以下は最初から作る例です。前のテーブル定義とは別に実行してください。
CREATE TABLE app_users (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
handle TEXT NOT NULL,
deleted_at TIMESTAMPTZ
);
CREATE UNIQUE INDEX app_users_active_handle_uq
ON app_users ((lower(handle)))
WHERE deleted_at IS NULL;
WHERE deleted_at IS NULL はインデックスに含める行の条件です。この条件に当てはまる現役行だけにUNIQUE（一意性）が適用されます。
lower(handle) は大文字・小文字をそろえる式です。たとえば Alice と alice を同じユーザー名と扱いたい場合に使えます。これは式インデックスと呼ばれる仕組みです。大小文字を区別する仕様なら ON app_users(handle) に変更できます。
退会後の再登録を試す
INSERT INTO app_users(handle) VALUES ('Alice');
-- 行は残し、削除日時だけを設定
UPDATE app_users
SET deleted_at = now()
WHERE lower(handle) = 'alice' AND deleted_at IS NULL;
-- 退会済みなので同じユーザー名で再登録できる
INSERT INTO app_users(handle) VALUES ('alice');
-- 現役ユーザーと衝突するためエラー（SQLSTATE 23505）
INSERT INTO app_users(handle) VALUES ('ALICE');
23505 はUNIQUE違反のSQLSTATE（DBのエラー種別を表すコード）です。APIでは、このコードを検出して「そのユーザー名は使用中です」といった業務上のエラーに変換します。
アプリから「SELECTで空きを確認→INSERT」と2回問い合わせるだけでは、別リクエストが確認と挿入の間に割り込むことがあります。同時実行時の最終的な保証はDB制約に任せるのが重要です。
ON CONFLICTと組み合わせる
重複時にエラーとせず挿入を省略したい場合、次のように書けます。
INSERT INTO app_users(handle)
VALUES ('ALICE')
ON CONFLICT ((lower(handle))) WHERE deleted_at IS NULL
DO NOTHING
RETURNING id;
ON CONFLICT はUNIQUE違反時の代替処理です。DO NOTHING は挿入をやめ、RETURNING は実際に挿入された行の値を返します。衝突した場合、結果は0行です。
今回は部分UNIQUEインデックスのキー式と条件を指定する必要があります。部分UNIQUEインデックスは通常のUNIQUE制約名ではないため、インデックス名を ON CONFLICT ON CONSTRAINT に指定する方法とは区別してください。
検索時にもWHERE条件をそろえる
PREPARE find_active_user(text) AS
SELECT id, handle
FROM app_users
WHERE deleted_at IS NULL
AND lower(handle) = lower($1);
EXECUTE find_active_user('Alice');
PREPARE はパラメーター付きSQLを準備する操作、$1 は最初の引数です。JavaのJDBCでは PreparedStatement に ? を書いて値を設定することに相当します。
部分インデックスは、クエリのWHERE条件がインデックスの対象条件を満たすとPostgreSQLが計画時に判断できる場合に利用候補となります。そのため deleted_at IS NULL を明示しましょう。検索するユーザー名の値をパラメーター化するのは問題ありませんが、インデックスの条件自体をパラメーター化すると対応を推論できない場合があります。
実際に使われるかどうかは表の件数や統計情報にも左右されます。
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM app_users
WHERE deleted_at IS NULL AND lower(handle) = 'alice';
EXPLAIN は実行計画を見る命令です。ANALYZE を付けるとSQLを実際に実行して測定するため、更新系SQLに使用するときは注意してください。
本番データへ導入する際のハマりどころ
1. 既存の重複を確認する。 既に現役ユーザーに重複があるとUNIQUEインデックスは作成できません。
SELECT lower(handle) AS normalized_handle, count(*) AS active_count
FROM app_users
WHERE deleted_at IS NULL
GROUP BY lower(handle)
HAVING count(*) > 1;
2. 大きなテーブルではロックを考慮する。 通常のインデックス作成は書き込みを待たせる場合があります。サービス運用中に追加するなら、次の CONCURRENTLY が候補です。最初のCREATE INDEXとの二重実行ではなく、既存テーブルへ導入するときの代替手順です。
CREATE UNIQUE INDEX CONCURRENTLY app_users_active_handle_uq
ON app_users ((lower(handle)))
WHERE deleted_at IS NULL;
CONCURRENTLY は書き込みを止めにくい一方、処理に時間がかかり、追加の負荷が発生することがあります。トランザクションブロック内では実行できません。 マイグレーションツールが自動でトランザクションを張る場合は設定を確認します。また、構築に失敗すると INVALID 状態のインデックスが残ることがあるため、エラー原因の解消後に状態を確認して復旧します。
3. 退会取り消しにも衝突がある。 退会済みのユーザーを復活させようと deleted_at をNULLに戻すとき、既にそのユーザー名を他の現役ユーザーが使っていればUNIQUE違反になります。
使い分けの結論
| 要件 | 選び方 |
|---|---|
| 全行で同じ値を禁止 | 通常のUNIQUE制約 |
| 現役行だけで同じ値を禁止 | 部分UNIQUEインデックス |
| 大文字小文字を無視 |
lower()などの式インデックス（要件を確認） |
| 同時登録の競合を防ぐ | DBの一意性制約＋23505のハンドリング |
| 大規模な既存表に追加 | 事前重複チェック＋CONCURRENTLYの検討 |
「どの行の、どの値を一意にするか」をDBの定義に表現すると、アプリの実装と制約のズレを減らせます。