PostgreSQL批量插入时唯一约束失效的疑难问题

核心原因分析 #

最可能的问题出在float8（双精度浮点数）的近似存储特性：
PostgreSQL的唯一约束依赖精确的字节级匹配，但float8是基于IEEE 754标准的近似数值类型，两个肉眼看起来完全相同的数值，实际二进制存储可能存在微小的精度差异（比如数据导入、程序计算时的舍入误差）。这会导致：

• 批量插入时，数据库认为这些是不同值，绕过唯一约束允许插入；
• 查询时SELECT DISTINCT或GROUP BY显示的是近似后的字符串，看起来重复，但实际存储值不同；
• 手动删除一条后再插入，因为直接复用了现有行的精确存储值，所以触发唯一约束冲突。

另外也需要排查varchar字段的隐性字符问题（比如前后空格、不可见Unicode字符），但结合你手动插入会触发冲突的现象，float8的精度问题是核心。

排查验证方法 #

1. 检查重复行的float8字段精确存储值：

   SELECT 
     encode(cast(float_col1 as bytea), 'hex') as float1_hex,
     encode(cast(float_col2 as bytea), 'hex') as float2_hex,
     encode(cast(float_col3 as bytea), 'hex') as float3_hex,
     varchar_col1, varchar_col2
   FROM your_table
   WHERE -- 筛选你认为重复的行的条件
   

   执行后会发现，看似相同的float字段，十六进制表示完全不同。

2. 验证varchar字段是否有隐性字符：

   SELECT 
     varchar_col1, length(varchar_col1), octet_length(varchar_col1),
     varchar_col2, length(varchar_col2), octet_length(varchar_col2)
   FROM your_table
   WHERE -- 筛选重复行条件
   

   如果length和octet_length结果不一致，说明存在多字节的不可见字符；如果length大于实际可见字符长度，说明有前后空格。

解决方案 #

1. 替换float8为精确数值类型（推荐） #

将float8字段改为numeric类型（精确存储），适合需要严格唯一性匹配的场景：

-- 先删除原唯一约束
ALTER TABLE your_table DROP CONSTRAINT your_unique_constraint_name;

-- 修改float8字段为numeric（根据业务需求指定精度，比如numeric(18,6)）
ALTER TABLE your_table ALTER COLUMN float_col1 TYPE numeric(18,6);
ALTER TABLE your_table ALTER COLUMN float_col2 TYPE numeric(18,6);
ALTER TABLE your_table ALTER COLUMN float_col3 TYPE numeric(18,6);

-- 重建唯一约束
ALTER TABLE your_table ADD CONSTRAINT your_unique_constraint_name UNIQUE (varchar_col1, varchar_col2, float_col1, float_col2, float_col3);

2. 保留float8但统一精度处理 #

如果必须使用float8，插入时对字段做截断/四舍五入，确保数值存储一致：

• 插入语句中统一处理float字段：

  INSERT INTO your_table (varchar_col1, varchar_col2, float_col1, float_col2, float_col3)
  VALUES 
    ('val1', 'val2', round(123.456789, 6), round(987.654321, 6), round(111.222333, 6)),
    -- 更多批量数据
  ON CONFLICT DO NOTHING;
  

• 同时将唯一约束建立在处理后的值上（通过函数索引实现）：

  CREATE UNIQUE INDEX idx_unique_your_table ON your_table (
    varchar_col1, 
    varchar_col2, 
    round(float_col1, 6), 
    round(float_col2, 6), 
    round(float_col3, 6)
  );
  

3. 处理varchar字段的隐性字符 #

如果排查出varchar字段有隐性字符，插入时统一清理：

INSERT INTO your_table (varchar_col1, varchar_col2, float_col1, float_col2, float_col3)
VALUES 
  ('val1', trim(' val2 '), ...), -- 去除前后空格
  -- 更多批量数据
ON CONFLICT DO NOTHING;

也可以建立基于trim后的函数索引来确保唯一性：

CREATE UNIQUE INDEX idx_unique_your_table ON your_table (
  varchar_col1, 
  trim(varchar_col2), 
  round(float_col1, 6), 
  round(float_col2, 6), 
  round(float_col3, 6)
);

恢复批量插入效率 #

解决上述精度或字符问题后，即可恢复使用INSERT ... ON CONFLICT DO NOTHING的批量插入方式，无需逐条插入，效率会回到正常水平。

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