PostgreSQL 15复制表时如何忽略唯一约束错误并跳过重复行？

以下是几种针对该场景的可靠解决方案，可高效跳过重复行完成数据复制：

方法1：使用INSERT ... ON CONFLICT DO NOTHING #

这是最直接的处理方式，利用PostgreSQL的冲突处理语法，插入时自动跳过违反唯一约束的行：

INSERT INTO mycopy (col1, col2, unique_col, ...) -- 列出所有需要复制的列
SELECT col1, col2, unique_col, ...
FROM mytable
ON CONFLICT (unique_col) DO NOTHING; -- 指定唯一约束对应的列

若唯一约束是多列组合约束，只需将列名组合传入：

ON CONFLICT (col_a, col_b) DO NOTHING;

该语句会尝试插入mytable的所有行，遇到约束冲突时直接跳过，不会中断整个复制操作。

方法2：提前去重后插入 #

当mytable中重复数据量较大时，提前去重再插入可减少冲突检测开销，提升效率。

方式A：用DISTINCT ON（PostgreSQL特有） #

DISTINCT ON会针对指定列分组，仅保留每组的第一行，通过ORDER BY可指定保留哪一行（比如最新/最早的数据）：

INSERT INTO mycopy (col1, col2, unique_col, ...)
SELECT DISTINCT ON (unique_col) col1, col2, unique_col, ...
FROM mytable
ORDER BY unique_col, id DESC; -- 按id降序保留每组中最新的行

方式B：用窗口函数（通用SQL语法） #

若需兼容其他数据库，可使用ROW_NUMBER()窗口函数实现去重：

INSERT INTO mycopy (col1, col2, unique_col, ...)
SELECT col1, col2, unique_col, ...
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY unique_col ORDER BY id DESC) AS rn
    FROM mytable
) t
WHERE rn = 1; -- 仅保留每组的第一行

方法3：结合COPY处理超大量数据 #

当mytable数据量极大时，COPY命令的导入效率远高于普通INSERT，可结合临时表使用：

1. 创建与mytable结构一致的临时表：

CREATE TEMP TABLE temp_mytable AS SELECT * FROM mytable LIMIT 0;

2. 将mytable的数据导入临时表（若从外部文件导入，直接替换为COPY temp_mytable FROM '/path/to/data.csv' WITH (FORMAT csv);）：

INSERT INTO temp_mytable SELECT * FROM mytable;

3. 从临时表去重后插入mycopy（可选用上述方法1或方法2的逻辑）：

INSERT INTO mycopy (col1, col2, unique_col, ...)
SELECT DISTINCT ON (unique_col) col1, col2, unique_col, ...
FROM temp_mytable
ORDER BY unique_col, id DESC;

临时表会在会话结束后自动删除，无需手动清理。

内容的提问来源于stack exchange，提问作者Joey Yi Zhao