批量插入数据至数据库触发唯一约束冲突错误求助

问题场景 #

向blse表批量插入数据时触发唯一约束冲突，但单条执行INSERT语句完全正常。错误信息如下：

ERROR: duplicate key value violates unique constraint "blse_pkey"
DETAIL: Key (date, employment_1000s, industry, state)=(2020-01-31, 2082.7, Total Nonfarm , Alabama) already exists.
SQL state: 23505

使用的批量插入脚本片段：

INSERT INTO blse VALUES('2020-01-31',436.5,'Government','Missouri'); 
INSERT INTO blse VALUES('2020-01-31',488.6,'Total Nonfarm','Montana'); 

核心原因 #

1. 批量数据集包含重复记录：你提供的脚本片段无重复，但实际批量执行的完整数据中，存在与表中已有(date, employment_1000s, industry, state)组合完全匹配的记录（如报错中的Alabama条目）。
2. 隐形字符导致的假重复：报错里的Total Nonfarm后带有大量空格，可能是批量数据中该字段存在多余空白字符，导致看似不同的记录实际命中唯一约束。
3. 逐个插入时的隐性跳过：手动逐条插入时可能无意识跳过了重复项，或执行顺序未触发冲突，但批量执行会一次性校验所有数据，问题直接暴露。

解决办法 #

1. 跳过重复记录继续插入 #

如果希望保留现有数据、忽略重复的新数据，使用ON CONFLICT DO NOTHING语法：

INSERT INTO blse (date, employment_1000s, industry, state)
VALUES
('2020-01-31',436.5,'Government','Missouri'),
('2020-01-31',488.6,'Total Nonfarm','Montana')
ON CONFLICT (date, employment_1000s, industry, state) DO NOTHING;

建议显式指定字段列表，避免表结构变更导致数据插入位置错误。

2. 覆盖重复记录（需更新现有数据时） #

如果要用新数据覆盖重复的旧数据，使用DO UPDATE：

INSERT INTO blse (date, employment_1000s, industry, state)
VALUES
('2020-01-31',436.5,'Government','Missouri'),
('2020-01-31',488.6,'Total Nonfarm','Montana')
ON CONFLICT (date, employment_1000s, industry, state) DO UPDATE 
SET 
  employment_1000s = EXCLUDED.employment_1000s,
  industry = EXCLUDED.industry,
  state = EXCLUDED.state;

3. 清理批量数据中的重复与隐形字符 #

• 检查批量数据内部重复（假设数据在临时表temp_blse中）：

SELECT date, employment_1000s, industry, state, COUNT(*)
FROM temp_blse
GROUP BY date, employment_1000s, industry, state
HAVING COUNT(*) > 1;

• 清理字段隐形字符（如去除前后空格）：

INSERT INTO blse (date, employment_1000s, industry, state)
VALUES
('2020-01-31',436.5,TRIM('Government'),'Missouri'),
('2020-01-31',488.6,TRIM('Total Nonfarm'),'Montana')
ON CONFLICT DO NOTHING;

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