PostgreSQL 的 ON CONFLICT 子句里可以携带 WHERE 条件，但这个条件的作用对象常常被误解。它不是在冲突发生后对候选行做二次筛选，而是用来精确匹配某个部分唯一索引的谓词。换句话说，数据库需要借助这个 WHERE 找到唯一性冲突应该由哪条索引承担，如果找不到对应的部分唯一索引，语句会直接报错。理解这一点，是利用条件唯一约束的关键。
WHERE 子句的语义：定位部分唯一索引
在 PostgreSQL 中，ON CONFLICT 的冲突目标可以显式写成列名或约束名，也可以完全省略让优化器自动判断。当冲突目标写成 ON CONFLICT(email) WHERE status = active 这种形式时，WHERE 并不是对冲突数据做状态过滤。数据库处理 INSERT 时，会先根据冲突目标找到一条唯一索引，然后在该索引上检查新行是否违反唯一性。对于部分唯一索引来说，唯一性只覆盖满足其 WHERE 谓词的那些行，所以冲突目标中的 WHERE 必须与索引定义中的 WHERE 完全等价，数据库才能建立对应关系。
如果表中只有一条普通唯一索引 email，并不存在带 WHERE 的部分唯一索引，那么给 ON CONFLICT 添加 WHERE 条件就是错误的。PostgreSQL 会抛出没有匹配约束或索引的错误。反过来，如果表中只有一条部分唯一索引，却省略 WHERE 只写 ON CONFLICT(email)，数据库同样无法匹配，因为该索引只对部分行生效，不能作为裸列的唯一索引使用。这个机制与很多人最初的理解正好相反：不是先找 email 冲突，再用 status 过滤，而是先用 WHERE 找到索引，再在索引范围内判断 email 是否冲突。
这种设计让条件唯一约束成为可能，但要求开发者在编写插入语句时，必须清楚地知道底层有哪些部分唯一索引，以及它们各自的谓词是什么。谓词不一致是线上最常见的问题之一，比如索引定义里写的是 lowercase(email)，但 ON CONFLICT 中写的是 email，虽然业务上看起来是同一种冲突，数据库却不会自动转换表达式。
创建部分唯一索引与基础冲突处理
建一个简单的用户表，并只在状态为 active 的用户上建立邮箱唯一索引，可以直观看到条件过滤的效果。下面这段 SQL 创建表结构和部分唯一索引：
CREATE TABLE users (
id bigserial PRIMARY KEY,
email text NOT NULL,
status text NOT NULL DEFAULT 'active',
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE status = 'active';
执行完成后，users 表里 email 字段的唯一性约束只对 status 等于 active 的记录有效。同一个邮箱可以存在多条 pending 或 banned 记录，但 active 状态只能保留一条。这样就从数据库层面避免了激活用户重复，而不需要在应用代码里额外做检查。
插入一条 active 用户，并声明冲突时不做任何事，可以写成：
INSERT INTO users (email, status)
VALUES ('dev@ipipp.com', 'active')
ON CONFLICT (email) WHERE status = 'active'
DO NOTHING;
第一次执行会正常插入。再次执行同样的语句时，由于新行的 email 和 status 都满足部分唯一索引的谓词，PostgreSQL 会命中 idx_users_email_active，检测到 email 冲突，于是执行 DO NOTHING，SQL 返回成功但不插入新行。如果第二次插入的 status 是 pending，则新行不满足索引谓词，不会触发冲突检查，插入会成功。这个行为正好体现了部分唯一索引的边界。
需要冲突时更新某个字段，可以改用 DO UPDATE。下面的语句在邮箱冲突且状态为 active 时更新 updated_at：
INSERT INTO users (email, status)
VALUES ('dev@ipipp.com', 'active')
ON CONFLICT (email) WHERE status = 'active'
DO UPDATE SET updated_at = now();
此时数据库会锁定冲突行并执行更新。需要注意的是，DO UPDATE 里不能修改参与冲突索引的列，或者说如果修改了 email 或 status，必须保证修改后的新行不会再次产生唯一性冲突，否则会报错。对于纯粹记录最后活跃时间的场景，更新 updated_at 已经足够。
常见错误：谓词不一致与索引匹配失败
最常见的错误是 ON CONFLICT 中的 WHERE 条件与索引谓词不完全一致。比如索引定义为 status = active，而插入语句写的是 status = pending，语句会直接报错，而不是静默跳过。下面这个例子可以复现问题：
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE status = 'active';
INSERT INTO users (email, status)
VALUES ('dev@ipipp.com', 'active')
ON CONFLICT (email) WHERE status = 'pending'
DO NOTHING;
执行插入时，PostgreSQL 会寻找一条唯一索引，其列包含 email，并且谓词与 WHERE status = pending 等价。显然 idx_users_email_active 的谓词是 active，两者不匹配，于是报错：没有匹配的唯一约束或索引。该错误在写入路径上可能会中断整个事务，因此在多状态业务里要特别小心。
另一种错误是省略 WHERE。如果 users 表上只有部分唯一索引 idx_users_email_active，没有完整的 email 唯一索引，那么执行 ON CONFLICT(email) 而不带 WHERE 也会失败。原因前面已经讨论过：裸列冲突目标无法对应到一条部分唯一索引。只有当 email 上存在完整的唯一索引时，不带 WHERE 的 ON CONFLICT(email) 才会选择那条完整索引，而不是部分索引。换句话说，一旦表里同时存在完整唯一索引和部分唯一索引，不带 WHERE 的冲突目标只会命中前者，后者不会参与冲突仲裁。
排查这类问题时，可以先查看表上所有索引的定义。psql 中可以直接使用 \d+ users 查看，或者查询系统目录：
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';
\d+ users
输出中会明确列出每个索引的完整定义，包括 WHERE 子句的原始文本。把插入语句中的 ON CONFLICT 条件与 indexdef 逐字符对比，通常能快速发现谓词不一致。比如索引定义中可能因为默认类型转换而显示为 WHERE status = 'active'::text，虽然与 WHERE status = 'active' 在语义上等价，但如果你在表达式索引或类型转换场景中自定义了条件，建议保持双方写法完全一致，减少不必要的等价判断风险。
部分唯一索引在多状态业务中的应用
部分唯一索引最典型的用途是让某个字段只在特定业务状态下保持唯一。以用户注册流程为例，用户可以提交多次申请，每次都会生成一条 pending 记录，此时 email 不需要唯一。当申请通过后，状态变为 active，此时必须保证一个邮箱只能有一个激活账号。如果使用普通的唯一约束，pending 记录之间也会互相冲突，无法表达这种条件唯一性。引入部分唯一索引后，数据库原生支持这种规则，应用层不需要先查询再插入，避免了并发下的竞态条件。
考虑一个用户表包含 email、status 和 activated_at。我们只希望激活用户邮箱唯一，同时允许历史 pending 和 banned 数据重复。可以这样建立索引：
CREATE UNIQUE INDEX idx_users_email_active
ON users (email)
WHERE status = 'active';
INSERT INTO users (email, status, activated_at)
VALUES ('dev@ipipp.com', 'active', now())
ON CONFLICT (email) WHERE status = 'active'
DO UPDATE SET activated_at = EXCLUDED.activated_at;
这段逻辑可以安全地处理重复激活操作：如果邮箱已经是 active，就更新激活时间，不会产生唯一性冲突；如果是首次激活，则插入新行。应用代码不需要使用锁或事务隔离级别来避免重复插入，数据库索引本身保证了并发安全。
另一个场景是软删除。很多表使用 deleted_at 字段标记删除，业务上希望只对未删除的记录保持唯一性。例如邀请码表，每个未删除的邀请码必须唯一，但删除后的旧邀请码可以重新生成同码记录。这时可以用 WHERE deleted_at IS NULL 建立部分唯一索引。这样既满足了业务约束，又不影响历史数据归档。需要注意，如果软删除操作把 deleted_at 设置为非空，该行会立即离开部分唯一索引，不会阻塞后续插入；但如果将来要恢复这条记录，就需要重新检查唯一性，可能产生冲突。
执行计划与性能排查
执行计划可以帮助确认 ON CONFLICT 实际使用了哪条索引。使用 EXPLAIN 加上 INSERT 语句，可以看到 Conflict Arbiter Index 这一行：
EXPLAIN (COSTS OFF)
INSERT INTO users (email, status)
VALUES ('dev@ipipp.com', 'active')
ON CONFLICT (email) WHERE status = 'active'
DO NOTHING;
计划中如果出现 Conflict Arbiter Index: idx_users_email_active，说明冲突目标匹配正确。如果执行前就报错，说明没有匹配索引；如果匹配到了另一条索引，则需要检查该索引的谓词和唯一性范围，确认是否与业务预期一致。在同时存在完整唯一索引和部分唯一索引的表上，这类混淆尤其常见。
从写入性能角度看，部分唯一索引只维护满足 WHERE 条件的行，因此索引体积更小，插入和更新时维护索引的代价也更低。对于大表来说，如果只有一小部分行需要唯一性约束，用部分唯一索引代替完整唯一索引可以明显减少存储和写入放大。但当冲突目标需要扫描的索引范围与整个表差不多时，优势就不明显。因此设计时最好结合实际的数据分布，避免为了使用条件唯一性而创建大量低选择性索引。
最后要强调的是，ON CONFLICT 中的 WHERE 条件必须被视为索引定义的一部分，而不是业务过滤的一部分。凡是需要条件唯一性的地方，都要先创建对应的部分唯一索引，再在写入语句中带上完全一致的谓词。这样才能让数据库正确识别冲突范围，避免报错和意外的重复数据。
PostgreSQLON CONFLICT部分唯一索引修改时间：2026-10-04 17:24:46