PostgreSQL 16使用DML跨模式复制表时触发唯一约束冲突错误

情况1：目标表并非空表 #

错误提示的重复键可能是因为目标表table_copy.job_sample实际上已有数据，并非你以为的空表。

• 先验证目标表数据量：

SELECT COUNT(*) FROM table_copy.job_sample;

• 如果结果大于0，清空目标表（若有外键关联需加CASCADE）：

TRUNCATE TABLE table_copy.job_sample;
-- 有外键时用：TRUNCATE TABLE table_copy.job_sample CASCADE;

• 重新执行你的INSERT语句：

INSERT INTO table_copy.job_sample (name, description, salary) 
SELECT name, description, salary FROM table.job;

情况2：源表存在重复的主键值 #

如果目标表确实为空，那问题出在源表table.job中name字段存在重复值（因为目标表的job_pkey是基于name的唯一约束）。

• 先排查源表的重复数据：

SELECT name, COUNT(*)
FROM table.job
GROUP BY name
HAVING COUNT(*) > 1;

• 根据需求处理重复数据后再执行复制：
  1. 仅保留每个name的一条记录（去重）：

  INSERT INTO table_copy.job_sample (name, description, salary)
  SELECT DISTINCT name, description, salary FROM table.job;
  

  2. 保留每个name的最新记录（假设表有自增id或时间戳字段）：

  INSERT INTO table_copy.job_sample (name, description, salary)
  SELECT DISTINCT ON (name) name, description, salary
  FROM table.job
  ORDER BY name, id DESC; -- 替换id为实际排序字段（如create_time）
  

  3. 先清理源表的重复数据（若允许修改源表）：

  DELETE FROM table.job
  WHERE id NOT IN (
      SELECT MAX(id) FROM table.job GROUP BY name
  );
  -- 清理后再执行原INSERT语句
  

情况3：目标表的主键约束设置错误 #

如果源表的主键并非name字段，而目标表误将name设为主键，导致冲突：

• 删除目标表的错误主键约束：

ALTER TABLE table_copy.job_sample DROP CONSTRAINT job_pkey;

• 添加与源表一致的主键约束（假设源表主键为id）：

ALTER TABLE table_copy.job_sample ADD CONSTRAINT job_pkey PRIMARY KEY (id);

• 重新执行复制语句。

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