PostgreSQL含重复值数据集的去重插入方案问询

针对你遇到的重复数据插入问题，我们可以分步骤处理，既清理已存在的重复项，又确保后续插入不会产生新的重复：

1. 清理已插入的重复数据（如果已经执行了初始INSERT） #

如果那些大量重复的记录已经进入数据库，我们可以用窗口函数筛选出重复项并删除，只保留每个唯一组合的第一条记录：

WITH duplicates AS (
    SELECT 
        ids_of_subject_section,
        -- 按唯一组合分组，给每组内的行编号
        ROW_NUMBER() OVER (
            PARTITION BY name_of_subject, section, topic, subtopic 
            ORDER BY ids_of_subject_section
        ) AS rn
    FROM public.table_subject_topics_exams
)
-- 删除编号大于1的重复行
DELETE FROM public.table_subject_topics_exams
WHERE ids_of_subject_section IN (
    SELECT ids_of_subject_section FROM duplicates WHERE rn > 1
);

这段代码会为每个(name_of_subject, section, topic, subtopic)组合的记录编号，只保留编号为1的最早插入的那条，删除其他重复项。

2. 插入唯一记录并防止未来重复 #

为了从根源上避免重复，首先给表添加唯一约束，让数据库直接拦截违反唯一性的插入操作：

ALTER TABLE public.table_subject_topics_exams
ADD CONSTRAINT unique_subject_section_topic_subtopic
UNIQUE (name_of_subject, section, topic, subtopic);

然后使用PostgreSQL的INSERT ... ON CONFLICT语法插入你的三个唯一记录，当记录已存在时自动跳过插入：

INSERT INTO public.table_subject_topics_exams (name_of_subject, section, topic, subtopic)
VALUES 
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
ON CONFLICT (name_of_subject, section, topic, subtopic) DO NOTHING;

ON CONFLICT ... DO NOTHING的作用是：当插入的记录与已有的唯一约束冲突时，跳过该条记录，不会抛出错误，也不会修改现有数据。

备选方案：不添加唯一约束时的插入方式（不推荐） #

如果因为特殊原因无法添加唯一约束，也可以用INSERT ... SELECT结合NOT EXISTS来实现仅插入不存在的记录：

INSERT INTO public.table_subject_topics_exams (name_of_subject, section, topic, subtopic)
SELECT s.name_of_subject, s.section, s.topic, s.subtopic
FROM (
    VALUES 
        ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
        ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
        ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
) AS s(name_of_subject, section, topic, subtopic)
WHERE NOT EXISTS (
    SELECT 1
    FROM public.table_subject_topics_exams t
    WHERE t.name_of_subject = s.name_of_subject
      AND t.section = s.section
      AND t.topic = s.topic
      AND t.subtopic = s.subtopic
);

不过还是强烈建议添加唯一约束，它能持续保护表数据的唯一性，避免后续其他操作意外插入重复项。

内容的提问来源于stack exchange，提问作者Владимир Кузовкин