PostgreSQL唯一约束未触发错误却出现重复数据问题求助

• 隐形字符导致视觉重复：两个name值看起来完全一致，但实际包含空格、制表符、换行符、全角/半角差异等隐形字符，导致字节内容不同。可以通过以下SQL查看字符的字节长度和十六进制编码，确认是否存在差异：

  select name, octet_length(name), encode(name, 'hex') from my_table where name = 'some_value';
  

• 唯一索引损坏：PostgreSQL的唯一约束依赖对应的唯一索引实现，如果索引损坏，会导致约束检查失效。可以尝试重建索引修复：

  REINDEX INDEX my_table_name_key;
  

  重建后再次检查重复数据是否存在，同时测试插入操作是否正常触发约束。

• 约束未验证现有数据：如果该唯一约束是后期添加的，且添加时使用了NOT VALID参数，那么约束仅对新插入的数据生效，不会检查已存在的历史数据。可以通过以下SQL查看约束是否已验证：

  select convalidated from pg_constraint where conname = 'my_table_name_key';
  

  如果返回false，需要执行以下命令验证现有数据并启用完整约束：

  ALTER TABLE my_table VALIDATE CONSTRAINT my_table_name_key;
  

• 并发事务竞态（极端场景）：虽然log_statement='all'会记录所有语句，但如果两个事务在极短时间内同时插入相同值，且事务提交顺序出现异常（比如索引更新延迟），可能导致约束检查失效。可以检查PostgreSQL日志中的事务提交时间线，同时排查是否存在未提交的长事务。

• 字符排序规则差异：如果name列的排序规则允许某些字符被视为等价，但实际存储的字节不同，可能出现分组时合并但插入时不触发约束的情况（反之也可能）。可以查看列的排序规则：

  select collation from pg_attribute where attrelid = 'my_table'::regclass and attname = 'name';
  

  若排序规则不是严格按字节比较（比如C排序规则），可以尝试用字节级比较确认是否真的重复：

  select name from my_table where name::bytea = 'some_value'::bytea;
  

内容的提问来源于stack exchange，提问作者Adrian Azoitei