PHP小码农

Appearance

唯一索引 ​

唯一索引（unique index）是 B-Tree 的第二个核心能力。它不只是「查得快」，更承担着数据完整性职责——主键、唯一约束、UPSERT 的冲突检测，背后全都是唯一索引。

但唯一索引有不少容易踩的坑：NULL 为什么可以重复？软删除后怎么保证「已删除的不算，未删除的才唯一」？ON CONFLICT 到底靠什么判断冲突？本节把 CREATE UNIQUE INDEX 的语法、NULL 语义、部分唯一索引、表达式唯一索引、UPSERT 依赖关系、冲突排查逐条讲透。

前置知识

阅读本节前，建议先了解：多列索引

基础概念 ​

唯一约束背后一定有一个唯一索引 ​

PostgreSQL 里，UNIQUE 约束和 PRIMARY KEY 约束都必须由一个唯一索引来支撑：

sql
ALTER TABLE t ADD CONSTRAINT t_email_key UNIQUE (email);

-- 等价于
CREATE UNIQUE INDEX t_email_key ON t (email);

你可以直接在系统表里看到这个索引：

sql
SELECT conname, contype, conindid::regclass AS backing_index
FROM pg_constraint
WHERE conrelid = 'users'::regclass;
text
    conname    | contype | backing_index
---------------+---------+---------------
 users_pkey    | p       | users_pkey
 users_email_key | u     | users_email_key
(2 rows)

conindid 就是支撑该约束的唯一索引。

唯一索引 vs UNIQUE 约束 ​

两者在「拒绝重复值」上等价，但在能力上有明确差异：

维度CREATE UNIQUE INDEXUNIQUE 约束
强制唯一性是是
可延迟（DEFERRABLE）否是
可被外键（FOREIGN KEY）引用否是
可出现于 \d 的 Constraints 段否是
可 ON CONFLICT ON CONSTRAINT否是
可被 pg_dump 还原为约束否是
支持部分索引（WHERE）是否
支持表达式（(expr)）是否
建索引方式可 CONCURRENTLY内部建索引，也可先建索引再 USING INDEX

一句话：要业务语义用约束，要灵活唯一性用唯一索引。 部分唯一索引与表达式唯一索引是「约束做不到、只有索引能做到」的两个能力。

NULL 与唯一性：默认允许重复 ​

这是唯一索引最容易误解的地方：默认情况下，多个 NULL 不算冲突。

sql
CREATE UNIQUE INDEX uq_demo_email ON users (email);

-- 以下两行都能插入（email 都是 NULL）
INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);   -- 成功！

原因是 SQL 标准里 NULL 表示「未知」，两个未知值不相等，因此不构成重复。所以 UNIQUE 约束下 NULL 可以出现任意多次。

从 PostgreSQL 15 起，可以用 NULLS NOT DISTINCT 改变这个语义：

写法NULL 语义多个 NULL
默认（NULLS DISTINCT）NULL 互不相等允许
NULLS NOT DISTINCTNULL 视为相等拒绝第二个

多列唯一索引的语义 ​

多列唯一索引要求「所有键列的组合唯一」，单个列可以重复：

text
唯一索引 (tenant, username)

(t1, alice)   ✓
(t1, bob)     ✓
(t2, alice)   ✓   ← alice 重复，但 tenant 不同，组合不重复
(t1, alice)   ✗   冲突

部分唯一索引：条件式唯一 ​

部分唯一索引（partial unique index）只对满足 WHERE 的行施加唯一性。这是实现「软删除后仍唯一」的标准手法：

sql
CREATE UNIQUE INDEX uq_users_active_email
  ON users (email)
  WHERE deleted_at IS NULL;

被软删除的行（deleted_at 非空）不参与唯一性检查，因此同一邮箱可以有多条历史已删记录。

表达式唯一索引 ​

唯一索引可以建在表达式上，实现「大小写不敏感唯一」等需求：

sql
CREATE UNIQUE INDEX uq_users_lower_email ON users (lower(email));

此时 Alice@x.com 与 alice@x.com 被视为冲突。

表达式必须 IMMUTABLE

lower() 是 IMMUTABLE，可以用；now()、random()、to_char(ts, 'YYYY') 等依赖外部状态的函数不行。

UPSERT 依赖唯一索引 ​

INSERT ... ON CONFLICT 的冲突检测完全依赖唯一索引或唯一约束：

场景依赖
ON CONFLICT (col) DO NOTHING/UPDATE需要 col 上有唯一索引（唯一索引推断）
ON CONFLICT ON CONSTRAINT name DO UPDATE需要名为 name 的唯一/主键约束
没有任何唯一索引ON CONFLICT 直接报错
text
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

还有一条官方警告值得记住：CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY 期间，同一表上的 INSERT ... ON CONFLICT 可能意外失败并报唯一性冲突。所以生产上做在线索引变更时，要留意依赖 UPSERT 的写入路径。

冲突错误：SQLSTATE 23505 ​

唯一性冲突的标准错误码是 23505（unique_violation）：

text
ERROR:  duplicate key value violates unique constraint "uq_users_email"
DETAIL:  Key (email)=(a@b.com) already exists.
SQLSTATE: 23505

应用层通常按这个码做「重复则忽略 / 改为更新」的分支判断。

唯一性检查的代价 ​

唯一性不是免费的：

影响说明
写放大每次插入都要在索引里查找是否存在同键
索引体积唯一索引无法对重复键做强去重优化（因为键本就必须唯一）
不可延迟唯一索引不支持 DEFERRABLE，批量导入时顺序敏感
并发建索引风险CREATE UNIQUE INDEX CONCURRENTLY 可能因存量重复数据失败

语法与用法 ​

text
-- 基础唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名);

-- 多列唯一
CREATE UNIQUE INDEX 索引名 ON 表名 (列1, 列2);

-- NULL 视为相等（PG 15+）
CREATE UNIQUE INDEX 索引名 ON 表名 (列名) NULLS NOT DISTINCT;

-- 部分唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 (列名) WHERE 条件;

-- 表达式唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名 ((lower(列名)));

-- 在线建立
CREATE UNIQUE INDEX CONCURRENTLY 索引名 ON 表名 (列名);

-- 唯一约束（背后自动建唯一索引）
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE (列名);

-- 唯一约束 + NULL 视为相等
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE NULLS NOT DISTINCT (列名);

-- 把已有唯一索引提升为约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名 UNIQUE USING INDEX 索引名;

最小可运行示例：

sql
DROP TABLE IF EXISTS demo_unique;

CREATE TABLE demo_unique (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

CREATE UNIQUE INDEX uq_demo_unique_email ON demo_unique (email);
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX

详细说明 ​

通过系统表确认约束与索引的对应关系 ​

sql
DROP TABLE IF EXISTS users CASCADE;

CREATE TABLE users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL,
  name  text NOT NULL
);

ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);
text
DROP TABLE
CREATE TABLE
ALTER TABLE
sql
SELECT
  c.conname                       AS constraint_name,
  c.contype                       AS type,
  c.conindid::regclass            AS backing_index,
  pg_get_constraintdef(c.oid)     AS definition
FROM pg_constraint c
WHERE c.conrelid = 'users'::regclass
ORDER BY c.conname;
text
  constraint_name  | type | backing_index  |       definition
-------------------+------+----------------+-------------------------
 users_email_key   | u    | users_email_key | UNIQUE (email)
 users_pkey        | p    | users_pkey      | PRIMARY KEY (id)
(2 rows)

不能直接删掉这个索引：

sql
DROP INDEX users_email_key;
text
ERROR:  cannot drop index users_email_key because constraint users_email_key on table users requires it
HINT:  You can drop constraint users_email_key on table users instead.

正确的删除方式：

sql
ALTER TABLE users DROP CONSTRAINT users_email_key;
text
ALTER TABLE

唯一索引与 NULL：实测 ​

先看一个 NOT NULL 列上的唯一索引：

sql
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice');
text
INSERT 0 1

重复插入直接冲突：

sql
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice2');
text
ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(a@b.com) already exists.

现在把列改为可空，观察 NULL 的行为：

sql
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

INSERT INTO users (email, name) VALUES (NULL, 'NoMail1');
INSERT INTO users (email, name) VALUES (NULL, 'NoMail2');
text
ALTER TABLE
INSERT 0 1
INSERT 0 1

两个 NULL 都插进去了——这正是「默认 NULL 互不相等」的体现。

统计一下 NULL 行：

sql
SELECT count(*) AS total, count(email) AS non_null FROM users;
text
 total | non_null
-------+----------
     3 |        1
(1 row)

count(email) 只算非 NULL，得到 1，说明有 2 行是 NULL。

NULLS NOT DISTINCT（PG 15+） ​

如果业务要求「NULL 也只能有一个」，用 NULLS NOT DISTINCT：

sql
DROP TABLE IF EXISTS nd_demo;

CREATE TABLE nd_demo (
  id  integer GENERATED ALWAYS AS IDENTITY,
  code text
);

CREATE UNIQUE INDEX uq_nd_demo_code
  ON nd_demo (code) NULLS NOT DISTINCT;

INSERT INTO nd_demo (code) VALUES (NULL);
INSERT INTO nd_demo (code) VALUES (NULL);
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "uq_nd_demo_code"
DETAIL:  Key (code)=(null) already exists.

第二个 NULL 被拒绝。

也可以用约束形式：

sql
ALTER TABLE nd_demo ADD CONSTRAINT nd_demo_code_key UNIQUE NULLS NOT DISTINCT (code);
text
ALTER TABLE

对照表：

定义多行 NULL多行相同非空值
默认 UNIQUE允许拒绝
UNIQUE NULLS NOT DISTINCT拒绝拒绝

PG 15 之前怎么办

老版本没有 NULLS NOT DISTINCT，只能用部分索引变通：把 NULL 转成一个哨兵值，或者用 COALESCE 表达式索引：

sql
CREATE UNIQUE INDEX uq_nd_demo_code_coalesce
  ON nd_demo (COALESCE(code, '<NULL>'));

部分唯一索引：软删除后唯一 ​

最经典的生产需求：「同一邮箱只能注册一次，但用户注销后可以重新注册」。用软删除 + 部分唯一索引：

sql
DROP TABLE IF EXISTS accounts CASCADE;

CREATE TABLE accounts (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email      text NOT NULL,
  deleted_at timestamptz
);

CREATE UNIQUE INDEX uq_accounts_active_email
  ON accounts (email)
  WHERE deleted_at IS NULL;
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX
INSERT 0 1

现在插入三条同邮箱的记录：

sql
INSERT INTO accounts (email) VALUES ('a@b.com');
INSERT INTO accounts (email, deleted_at) VALUES ('a@b.com', now() - interval '1 day');
INSERT INTO accounts (email, deleted_at) VALUES ('a@b.com', now() - interval '2 days');
text
INSERT 0 1
INSERT 0 1
INSERT 0 1

全部成功。再插一条「活跃」的同邮箱：

sql
INSERT INTO accounts (email) VALUES ('a@b.com');
text
ERROR:  duplicate key value violates unique constraint "uq_accounts_active_email"
DETAIL:  Key (email)=(a@b.com) already exists.

只有活跃行受约束，历史注销记录不受影响。

一个更精细的需求：「允许在注销后重建，但如果同一天注销两次则不行」：

sql
CREATE UNIQUE INDEX uq_accounts_email_deldate
  ON accounts (email, (deleted_at::date))
  WHERE deleted_at IS NOT NULL;
text
CREATE UNIQUE INDEX

这就是「表达式 + 部分」的组合用法。

部分唯一索引实现「最多一个 NULL」 ​

需求：一个用户只能有一个「默认地址」（其他地址 is_default 为 false，可多个）。

sql
DROP TABLE IF EXISTS addresses CASCADE;

CREATE TABLE addresses (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id    integer NOT NULL,
  label      text NOT NULL,
  is_default boolean NOT NULL DEFAULT false
);

CREATE UNIQUE INDEX uq_addresses_one_default
  ON addresses (user_id)
  WHERE is_default;

INSERT INTO addresses (user_id, label, is_default) VALUES (1, 'home', true);
INSERT INTO addresses (user_id, label, is_default) VALUES (1, 'office', false);
INSERT INTO addresses (user_id, label, is_default) VALUES (1, 'other', false);
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX
INSERT 0 1
INSERT 0 1
INSERT 0 1

再插第二个默认地址：

sql
INSERT INTO addresses (user_id, label, is_default) VALUES (1, 'gym', true);
text
ERROR:  duplicate key value violates unique constraint "uq_addresses_one_default"
DETAIL:  Key (user_id)=(1) already exists.

部分唯一索引的写法技巧

WHERE is_default 就是 WHERE is_default = true 的简写。部分索引的 WHERE 谓词只要能被查询条件蕴含，就能被用上——PG 9.6 起部分唯一索引还能服务于 ON CONFLICT 的索引推断。

表达式唯一索引：大小写不敏感唯一 ​

sql
DROP TABLE IF EXISTS sys_users CASCADE;

CREATE TABLE sys_users (
  id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email text NOT NULL
);

CREATE UNIQUE INDEX uq_sys_users_lower_email
  ON sys_users (lower(email));

INSERT INTO sys_users (email) VALUES ('Alice@Example.com');
text
DROP TABLE
CREATE TABLE
CREATE UNIQUE INDEX
INSERT 0 1

不同大小写但同值被拒绝：

sql
INSERT INTO sys_users (email) VALUES ('alice@example.com');
text
ERROR:  duplicate key value violates unique constraint "uq_sys_users_lower_email"
DETAIL:  Key (lower(email))=(alice@example.com) already exists.

注意 DETAIL 里显示的是 lower(email) 的结果，说明优化器确实按表达式匹配。

一个常见错误是「建了表达式唯一索引，但查询里不用同样的表达式」：

sql
-- 能走唯一索引（表达式匹配）
SELECT id FROM sys_users WHERE lower(email) = 'alice@example.com';

-- 走不了唯一索引（普通索引也帮不上，因为列上没有普通索引）
SELECT id FROM sys_users WHERE email = 'alice@example.com';

唯一索引与 UPSERT ​

ON CONFLICT 的冲突检测必须有一个唯一索引作为「仲裁者（arbiter）」。

sql
DROP TABLE IF EXISTS counters CASCADE;

CREATE TABLE counters (
  name  text PRIMARY KEY,
  value bigint NOT NULL DEFAULT 0
);

INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT (name) DO UPDATE
  SET value = counters.value + EXCLUDED.value;
text
DROP TABLE
CREATE TABLE
INSERT 0 1

再执行一次，触发更新分支：

sql
INSERT INTO counters (name, value) VALUES ('hits', 5)
ON CONFLICT (name) DO UPDATE
  SET value = counters.value + EXCLUDED.value
RETURNING name, value;
text
 name | value
------+-------
 hits |     6
(1 row)

这里 ON CONFLICT (name) 靠的就是主键 counters_pkey 这个唯一索引。可以指定多个列做「索引推断」：

sql
CREATE UNIQUE INDEX uq_counters_name_bucket ON counters (name);
-- 用 ON CONFLICT (name) 即可，无需知道索引名

用约束名的方式（要求确实是约束）：

sql
INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT ON CONSTRAINT counters_pkey DO UPDATE
  SET value = counters.value + EXCLUDED.value;
text
INSERT 0 1

部分唯一索引也能做仲裁者（PG 9.6+）：

sql
CREATE UNIQUE INDEX uq_accounts_active_email2
  ON accounts (email)
  WHERE deleted_at IS NULL;

INSERT INTO accounts (email) VALUES ('c@d.com')
ON CONFLICT (email) WHERE deleted_at IS NULL DO NOTHING;
text
CREATE UNIQUE INDEX
INSERT 0 1

冲突仲裁者的选择规则

  • ON CONFLICT (col, ...) 是索引推断：在唯一索引中找「键列集合恰好等于所列列」的那个。列顺序无关，但集合必须一致。
  • ON CONFLICT ON CONSTRAINT name 要求 name 是约束（UNIQUE / PRIMARY KEY / EXCLUDE），不能是裸索引。
  • 官方建议：优先用索引推断，因为约束名可能随环境变化，而列组合是稳定的。

唯一性冲突的排查 ​

生产上遇到 23505 时，第一步是定位到底是哪个索引：

sql
SELECT
  c.conname,
  c.contype,
  pg_get_constraintdef(c.oid)
FROM pg_constraint c
WHERE c.conrelid = 'users'::regclass AND c.contype IN ('u', 'p');
text
  conname        | contype |  pg_get_constraintdef
-----------------+---------+-----------------------
 users_pkey      | p       | PRIMARY KEY (id)
 users_email_key | u       | UNIQUE (email)
(2 rows)

第二步是检查「建唯一索引之前是否已有重复数据」：

sql
SELECT email, count(*) AS dup_count
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING count(*) > 1
ORDER BY dup_count DESC
LIMIT 10;
text
 email | dup_count
-------+-----------
(0 rows)

有重复时，CREATE UNIQUE INDEX CONCURRENTLY 会失败并留下 INVALID 索引：

sql
CREATE UNIQUE INDEX CONCURRENTLY uq_users_email ON users (email);
text
ERROR:  could not create unique index "uq_users_email"
DETAIL:  Key (email)=(a@b.com) is duplicated.

清理残骸：

sql
SELECT c.relname AS invalid_index
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'users'::regclass AND NOT i.indisvalid;

DROP INDEX CONCURRENTLY uq_users_email;
text
 invalid_index
---------------
 uq_users_email
(1 row)
DROP INDEX

唯一索引的写代价与去重限制 ​

唯一索引在维护上比普通索引更贵：

sql
DROP TABLE IF EXISTS bench_uniq;

CREATE TABLE bench_uniq (
  id  bigint GENERATED ALWAYS AS IDENTITY,
  k   integer NOT NULL
);

INSERT INTO bench_uniq (k) SELECT g % 1000 FROM generate_series(1, 200000) AS g;

CREATE INDEX idx_bench_uniq_nouniq ON bench_uniq (k);
CREATE UNIQUE INDEX uq_bench_uniq_k ON bench_uniq (k);   -- 会失败，因为有重复
text
DROP TABLE
CREATE TABLE
INSERT 0 200000
CREATE INDEX
ERROR:  could not create unique index "uq_bench_uniq_k"
DETAIL:  Key (k)=(0) is duplicated.

改用唯一值再试：

sql
DROP TABLE IF EXISTS bench_uniq2;
CREATE TABLE bench_uniq2 (
  id bigint GENERATED ALWAYS AS IDENTITY,
  k  integer NOT NULL
);
INSERT INTO bench_uniq2 (k) SELECT g FROM generate_series(1, 200000) AS g;

CREATE UNIQUE INDEX uq_bench_uniq2_k ON bench_uniq2 (k);

SELECT pg_size_pretty(pg_relation_size('uq_bench_uniq2_k')) AS unique_index_size;
text
DROP TABLE
CREATE TABLE
INSERT 0 200000
CREATE UNIQUE INDEX
 unique_index_size
-------------------
 4408 kB

对比非唯一索引（同样 20 万行）：

sql
ANALYZE bench_uniq;
SELECT pg_size_pretty(pg_relation_size('idx_bench_uniq_nouniq')) AS nonunique_index_size;
text
 nonunique_index_size
----------------------
 1608 kB

差别来自去重（deduplication）：k % 1000 只有 1000 个不同值，每个值重复 200 次，去重后 posting list 极度紧凑；而唯一索引每个键只出现一次，无从去重。

用唯一索引替代 UNIQUE 约束的场景 ​

场景为什么必须用唯一索引
部分唯一（软删除、条件唯一）约束不支持 WHERE
表达式唯一（大小写不敏感）约束不支持表达式
先并发建索引再挂约束避免加约束时长时间持锁
只想保证唯一但不需要外键引用索引语义足够

「先并发建索引再挂约束」的标准三步：

sql
-- 1. 并发建唯一索引
CREATE UNIQUE INDEX CONCURRENTLY uq_accounts_email ON accounts (email);

-- 2. 挂成约束（瞬时完成）
ALTER TABLE accounts ADD CONSTRAINT accounts_email_key UNIQUE USING INDEX uq_accounts_email;

-- 3. 验证
SELECT conname, conindid::regclass FROM pg_constraint
WHERE conrelid = 'accounts'::regclass AND contype = 'u';
text
CREATE INDEX
ALTER TABLE
      conname       |  conindid
--------------------+-------------
 accounts_email_key | uq_accounts_email
(1 row)

实战示例 ​

场景：一个内容平台需要「软删除后邮箱可复用 + 大小写不敏感 + 支持 UPSERT 注册」，用最少的结构把这些需求全部实现。

第 1 步：建表

sql
DROP TABLE IF EXISTS app_users CASCADE;

CREATE TABLE app_users (
  id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email         text NOT NULL,
  display_name  text NOT NULL,
  status        text NOT NULL DEFAULT 'active',
  deleted_at    timestamptz,
  login_count   bigint NOT NULL DEFAULT 0,
  created_at    timestamptz NOT NULL DEFAULT now()
);
text
DROP TABLE
CREATE TABLE

第 2 步：建立部分表达式唯一索引（满足大小写不敏感 + 软删除可复用）

sql
CREATE UNIQUE INDEX CONCURRENTLY uq_app_users_active_lower_email
  ON app_users (lower(email))
  WHERE deleted_at IS NULL;
text
CREATE INDEX

验证索引有效：

sql
SELECT c.relname AS index_name, i.indisvalid, pg_get_indexdef(i.indexrelid) AS definition
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE i.indrelid = 'app_users'::regclass AND NOT i.indisprimary;
text
            index_name            | indisvalid |                                    definition
----------------------------------+------------+-----------------------------------------------------------------------------------
 uq_app_users_active_lower_email  | t          | CREATE UNIQUE INDEX uq_app_users_active_lower_email ON public.app_users USING btree
                                  |            |  (lower(email)) WHERE (deleted_at IS NULL)
(1 row)

第 3 步：验证大小写不敏感

sql
INSERT INTO app_users (email, display_name) VALUES ('Alice@Example.com', 'Alice');
INSERT INTO app_users (email, display_name) VALUES ('alice@example.com', 'Alice2');
text
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "uq_app_users_active_lower_email"
DETAIL:  Key (lower(email))=(alice@example.com) already exists.

第 4 步：验证软删除后可复用

sql
UPDATE app_users SET deleted_at = now(), status = 'deleted' WHERE id = 1;

INSERT INTO app_users (email, display_name) VALUES ('alice@example.com', 'Alice-New');
text
UPDATE 1
INSERT 0 1

注销后可重新注册，且可以再次注销：

sql
UPDATE app_users SET deleted_at = now() WHERE id = 2;
INSERT INTO app_users (email, display_name) VALUES ('ALICE@example.com', 'Alice-Third');
text
UPDATE 1
INSERT 0 1

第 5 步：用 UPSERT 实现幂等注册

需要一个明确的唯一索引作为仲裁者。这里用 lower(email) 的部分唯一索引配合部分冲突目标：

sql
INSERT INTO app_users (email, display_name, login_count)
VALUES ('bob@example.com', 'Bob', 1)
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL
DO UPDATE SET
  login_count = app_users.login_count + EXCLUDED.login_count,
  display_name = EXCLUDED.display_name
RETURNING id, email, login_count;
text
 id |      email       | login_count
----+------------------+-------------
  4 | bob@example.com  |           1
(1 row)

再执行一次，走更新分支：

sql
INSERT INTO app_users (email, display_name, login_count)
VALUES ('Bob@Example.com', 'Bob', 1)
ON CONFLICT (lower(email)) WHERE deleted_at IS NULL
DO UPDATE SET
  login_count = app_users.login_count + EXCLUDED.login_count,
  display_name = EXCLUDED.display_name
RETURNING id, email, login_count;
text
 id |      email       | login_count
----+------------------+-------------
  4 | bob@example.com  |           2
(1 row)

注意第二次用的邮箱大小写不同（Bob@Example.com），但因为索引建在 lower(email) 上，依然命中冲突并累加。

第 6 步：查看当前数据状态

sql
SELECT id, email, display_name, login_count, (deleted_at IS NULL) AS is_active
FROM app_users ORDER BY id;
text
 id |      email       | display_name | login_count | is_active
----+------------------+--------------+-------------+-----------
  1 | Alice@Example.com| Alice        |           0 | f
  2 | alice@example.com| Alice-New    |           0 | f
  3 | ALICE@example.com| Alice-Third  |           0 | t
  4 | Bob@Example.com  | Bob          |           2 | t
(4 rows)

同一邮箱的三条历史记录都保留，但仅有一条处于活跃状态。

第 7 步：用系统表汇总唯一索引

sql
SELECT
  i.indexrelid::regclass                          AS index_name,
  i.indisunique                                   AS is_unique,
  i.indisvalid                                    AS is_valid,
  pg_size_pretty(pg_relation_size(i.indexrelid))  AS size
FROM pg_index i
WHERE i.indrelid = 'app_users'::regclass
ORDER BY i.indexrelid::regclass::text;
text
            index_name            | is_unique | is_valid |  size
----------------------------------+-----------+----------+---------
 app_users_pkey                   | t         | t        | 8192 bytes
 uq_app_users_active_lower_email  | t         | t        | 16 kB
(2 rows)

第 8 步：清理

sql
DROP TABLE app_users;
DROP TABLE counters;
DROP TABLE addresses;
DROP TABLE accounts;
DROP TABLE sys_users;
DROP TABLE bench_uniq;
DROP TABLE bench_uniq2;
DROP TABLE nd_demo;
DROP TABLE users;
DROP TABLE demo_unique;

注意事项 ​

  • 默认情况下多个 NULL 不冲突，别以为唯一索引能限制 NULL 重复。

    sql
    -- 这两行都能插入
INSERT INTO users (email) VALUES (NULL);
INSERT INTO users (email) VALUES (NULL);

  • 要限制 NULL 也唯一必须显式写 NULLS NOT DISTINCT（PG 15+）：

    sql
    CREATE UNIQUE INDEX uq_x ON t (col) NULLS NOT DISTINCT;

  • 唯一索引不能延迟（DEFERRABLE），批量导入时顺序敏感。要延迟只能用约束：

    sql
    -- 唯一索引不支持这个选项
-- ALTER TABLE t ADD CONSTRAINT ... UNIQUE (col) DEFERRABLE INITIALLY DEFERRED;

  • ON CONFLICT 必须有唯一索引或唯一/主键/排他约束，否则直接报错。

    sql
    -- 错误！email 上没有唯一索引
INSERT INTO users (email) VALUES ('a@b.com')
ON CONFLICT (email) DO NOTHING;
-- ERROR: there is no unique or exclusion constraint matching the ON CONFLICT specification

  • CREATE UNIQUE INDEX CONCURRENTLY 遇到存量重复数据会失败并留下 INVALID 索引，必须先清洗数据。

    sql
    SELECT email, count(*) FROM users GROUP BY email HAVING count(*) > 1;

  • CREATE INDEX CONCURRENTLY / REINDEX CONCURRENTLY 期间，同一表上的 UPSERT 可能意外报唯一冲突（官方明确警告）。在线索引变更要避开高频 UPSERT 路径。

  • 不能直接 DROP INDEX 删除约束背后的索引，要 ALTER TABLE DROP CONSTRAINT。

    sql
    -- 错误！
DROP INDEX users_email_key;

  • 表达式唯一索引必须与查询表达式逐字一致才能被利用。

    sql
    CREATE UNIQUE INDEX uq_x ON users (lower(email));
-- 命中：WHERE lower(email) = ?
-- 不命中：WHERE email = ?（除非还有别的索引）

  • 多列唯一索引允许单列重复，别指望它能保证单列唯一。

    sql
    -- (t1, alice) 与 (t2, alice) 可以共存
CREATE UNIQUE INDEX uq ON t (tenant, username);

  • 部分唯一索引要生效，查询条件必须逻辑上蕴含 WHERE 谓词：

    sql
    CREATE UNIQUE INDEX uq ON accounts (email) WHERE deleted_at IS NULL;
-- WHERE deleted_at IS NULL AND email = ? 可能走它
-- WHERE email = ? 单独时不走（无法确定 deleted_at 是否为 NULL）

  • 唯一索引上的去重优化价值低于普通索引（键本就不重复，无从合并）。

  • ON CONFLICT DO UPDATE 是确定性语句，一条语句不能两次影响同一行，否则报 cardinality violation。

    sql
    -- 危险！如果 VALUES 里有重复 key，会报错
INSERT INTO t (k, v) VALUES (1, 'a'), (1, 'b')
ON CONFLICT (k) DO UPDATE SET v = EXCLUDED.v;
-- ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time

最佳实践 ​

  • 业务唯一性优先用 UNIQUE 约束表达（可延迟、可被外键引用），只有当需要 WHERE 或表达式时才退回唯一索引：

    sql
    ALTER TABLE users ADD CONSTRAINT users_email_key UNIQUE (email);

  • 软删除场景一律用部分唯一索引，这是最标准、最干净的方案：

    sql
    CREATE UNIQUE INDEX uq_users_active_email
  ON users (email) WHERE deleted_at IS NULL;

  • 大小写不敏感唯一用 lower(col) 表达式唯一索引，并在查询里统一使用同一表达式：

    sql
    CREATE UNIQUE INDEX uq_users_email_lower ON users (lower(email));

  • 「最多一条满足条件的行」用部分唯一索引实现，比应用层加锁简单可靠：

    sql
    CREATE UNIQUE INDEX uq_addr_one_default ON addresses (user_id) WHERE is_default;

  • 大表加唯一约束走「并发建索引 + USING INDEX 挂约束」三步，避免长锁：

    sql
    CREATE UNIQUE INDEX CONCURRENTLY uq_t_c ON t (c);
ALTER TABLE t ADD CONSTRAINT t_c_key UNIQUE USING INDEX uq_t_c;

  • 建唯一索引前先跑重复数据检查，避免中途失败留残骸：

    sql
    SELECT c1, c2, count(*) FROM t GROUP BY c1, c2 HAVING count(*) > 1;

  • UPSERT 用索引推断而非约束名，列组合比名字稳定：

    sql
    INSERT INTO counters (name, value) VALUES ('hits', 1)
ON CONFLICT (name) DO UPDATE SET value = counters.value + EXCLUDED.value;

  • 需要「NULL 也唯一」时显式 NULLS NOT DISTINCT，别依赖应用层判断：

    sql
    CREATE UNIQUE INDEX uq_nd ON nd_demo (code) NULLS NOT DISTINCT;

  • 应用层按 SQLSTATE 23505 做分支，而不是解析错误文本：

    text
    -- 伪代码
-- if (err.code == '23505') { ... }

  • 在线索引变更时暂停或改造依赖 ON CONFLICT 的写入路径，避免并发建索引期间的意外冲突失败。

  • 定期用系统表复核唯一索引的有效性：

    sql
    SELECT c.relname FROM pg_index i JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

下一节

继续学习：索引排序与 NULLS FIRST / LAST

参考链接 ​