PostgreSQL 16中INSERT ON CONFLICT失效，触发UniqueId唯一约束冲突

问题背景 #

一段稳定运行8年的PostgreSQL插入语句，从9.x版本升级到16版本后出现报错。

插入语句 #

insert into bt(g,accountnumber,InputDate,ValueDate,Amount,Currency,Code,Reason,Branch,Type,Reference,nl1,nl2,nl3,nl4,UniqueId,Balance,UniqueIdNew)
    (select 
        0 g,
        substring(l,1,13) accountnumber,
        TO_DATE(substring(l,15,10),'YYYY/MM/DD') InputDate,
        TO_DATE(substring(l,26,10),'YYYY/MM/DD') ValueDate,
        cast(REPLACE(substring(l,37,19),',','.') as double precision) Amount,
        substring(l,57,8) Currency,
        substring(l,66,5) Code,
        substring(l,71,35) Reason,
        substring(l,107,6) Branch,
        substring(l,114,6) Type,
        substring(l,121,20) Reference,
        substring(l,142,35) nl1,
        substring(l,178,35) nl2,
        substring(l,214,35) nl3,
        substring(l,250,35) nl4,
        substring(l,286,22) UniqueId,
        cast(REPLACE(substring(l,309,19),',','.') as double precision) Balance,
        substring(l,329,353-329) UniqueIdNew
     from statement 
    )
    on conflict (accountnumber,inputdate,valuedate,amount,balance,currency,code,branch,type,reference) do nothing;

环境说明 #

• 已创建包含accountnumber,inputdate,valuedate,amount,balance,currency,code,branch,type,reference列的唯一索引
• 表bt存在名为UniqueId_UNIQUE的唯一索引（对应UniqueId列）
• 经检查，不存在UniqueId相同但上述冲突列组合不同的记录

报错信息 #

ERROR:  duplicate key value violates unique constraint "idx_121286_dolibarredsce.UniqueId_UNIQUE"
DETAIL:  Key (uniqueid)=(304182960EL01P01658601) already exists.
SQL state: 23505

可能原因及解决思路 #

1. ON CONFLICT子句的作用范围限制 #

ON CONFLICT仅处理你指定的唯一约束/索引对应的冲突，不会自动处理其他唯一约束的冲突。这个逻辑从PostgreSQL 9.5引入ON CONFLICT以来一直保持，并非16版本的bug。

之前9.x版本没报错，大概率是因为：

• 历史数据中UniqueId的重复恰好与指定的冲突列组合重复，被do nothing逻辑过滤
• 9.x版本的执行计划优先扫描指定的冲突索引，提前过滤了所有冲突行（包括触发UniqueId约束的行），而16版本的执行计划调整，导致先尝试插入触发了UniqueId的约束校验

2. 数据层面的隐性差异 #

虽然你检查过不存在UniqueId相同但冲突列组合不同的记录，但可能存在以下隐性问题：

• 浮点数精度问题：Amount和Balance是double precision类型，REPLACE(substring(...), ',', '.')转换后可能存在精度差异，导致你认为匹配的冲突列组合，实际在数据库中并不完全相等，因此没被ON CONFLICT过滤
• 字符串隐性差异：accountnumber、Branch等字符串列可能存在空格、大小写或编码差异，导致冲突列组合不匹配，但UniqueId确实重复

3. 执行计划的版本差异 #

PostgreSQL 16对查询优化器做了不少改进，执行计划可能与9.x版本不同：

• 9.x版本可能先通过指定的冲突索引做预检查，过滤掉所有冲突行
• 16版本可能选择先处理插入逻辑，再依次校验约束，导致UniqueId的约束先被触发报错

解决方法 #

• 提前过滤UniqueId重复行：在子查询中先排除UniqueId已存在的记录，避免触发对应的唯一约束：

  insert into bt(g,accountnumber,InputDate,ValueDate,Amount,Currency,Code,Reason,Branch,Type,Reference,nl1,nl2,nl3,nl4,UniqueId,Balance,UniqueIdNew)
      (select 
          0 g,
          substring(l,1,13) accountnumber,
          TO_DATE(substring(l,15,10),'YYYY/MM/DD') InputDate,
          TO_DATE(substring(l,26,10),'YYYY/MM/DD') ValueDate,
          cast(REPLACE(substring(l,37,19),',','.') as double precision) Amount,
          substring(l,57,8) Currency,
          substring(l,66,5) Code,
          substring(l,71,35) Reason,
          substring(l,107,6) Branch,
          substring(l,114,6) Type,
          substring(l,121,20) Reference,
          substring(l,142,35) nl1,
          substring(l,178,35) nl2,
          substring(l,214,35) nl3,
          substring(l,250,35) nl4,
          substring(l,286,22) UniqueId,
          cast(REPLACE(substring(l,309,19),',','.') as double precision) Balance,
          substring(l,329,353-329) UniqueIdNew
       from statement s
       where not exists (
           select 1 from bt 
           where bt.UniqueId = substring(s.l,286,22)
       )
      )
      on conflict (accountnumber,inputdate,valuedate,amount,balance,currency,code,branch,type,reference) do nothing;
  

• 排查数据隐性差异：针对报错的UniqueId，查询表中已存在的对应行，对比待插入行的冲突列是否完全一致，比如执行：

  SELECT accountnumber, InputDate, ValueDate, Amount, Balance, Currency, Code, Branch, Type, Reference 
  FROM bt WHERE UniqueId = '304182960EL01P01658601';
  

  再与待插入的对应字段值做精确对比，重点检查浮点数精度、字符串空格/大小写。
• 临时调整执行计划：如果是执行计划导致的问题，可以尝试临时设置SET enable_seqscan = off;，强制优化器使用索引扫描，看是否能回到9.x版本的执行逻辑，但这只是临时方案，建议优先从数据或语句层面解决。

内容的提问来源于stack exchange，提问作者George Kourtis