PostgreSQL中插入含唯一索引表数据后，获取各插入ID对应原插入次数的查询方法问询

嘿，这个需求很好解决，核心思路就是先统计你原始插入数据里每个唯一组合的出现次数，再和INSERT返回的ID结果关联起来就行。我给你两种实用的方法：

方法1：关联已返回的INSERT结果与原始插入统计 #

如果你已经拿到了INSERT语句返回的ID和对应组合，我们可以先把原始要插入的所有值列出来统计次数，再和返回结果做关联：

WITH original_inserts AS (
  SELECT 
    name_of_subject, 
    section, 
    topic, 
    subtopic,
    COUNT(*) AS insert_count
  FROM (
    VALUES 
      ('Algebra', 'Mathematics', 'Progressions', 'Number Sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
      ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
      ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
      ('Algebra', 'Mathematics', 'Progressions', 'Arithmetic Progression'),
      ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression'),
      ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
  ) AS t(name_of_subject, section, topic, subtopic)
  -- 统一转小写避免大小写不敏感导致的匹配失败（比如数据库返回的是小写结尾的sequences）
  GROUP BY LOWER(name_of_subject), LOWER(section), LOWER(topic), LOWER(subtopic),
           name_of_subject, section, topic, subtopic
),
insert_results AS (
  -- 把INSERT返回的结果放到这里
  VALUES
    (3, 'Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    (33, 'Algebra', 'Mathematics', 'Progressions', 'Arithmetic progression'),
    (37, 'Algebra', 'Mathematics', 'Progressions', 'Geometric progression')
) AS t(ids_of_subject_section, name_of_subject, section, topic, subtopic)
SELECT 
  ir.ids_of_subject_section AS id,
  oi.insert_count AS occurrence_times
FROM insert_results ir
JOIN original_inserts oi
  ON LOWER(ir.name_of_subject) = LOWER(oi.name_of_subject)
  AND LOWER(ir.section) = LOWER(oi.section)
  AND LOWER(ir.topic) = LOWER(oi.topic)
  AND LOWER(ir.subtopic) = LOWER(oi.subtopic);

方法2：执行INSERT的同时直接获取统计结果 #

如果是刚要执行INSERT，想一步得到每个ID的插入次数，可以把原始插入数据、INSERT操作、统计逻辑合并在一起：

WITH original_inserts AS (
  SELECT 
    name_of_subject, 
    section, 
    topic, 
    subtopic,
    COUNT(*) AS insert_count
  FROM (
    VALUES 
      ('Algebra', 'Mathematics', 'Progressions', 'Number Sequences'),
      ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
      -- 这里把所有要插入的行都列出来，和INSERT里的VALUES保持一致
      ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
  ) AS t(name_of_subject, section, topic, subtopic)
  GROUP BY LOWER(name_of_subject), LOWER(section), LOWER(topic), LOWER(subtopic),
           name_of_subject, section, topic, subtopic
)
SELECT 
  ir.ids_of_subject_section AS id,
  oi.insert_count AS occurrence_times
FROM (
  INSERT INTO table_subject_topics_exams (name_of_subject, section, topic, subtopic)
  VALUES 
    ('Algebra', 'Mathematics', 'Progressions', 'Number Sequences'),
    ('Algebra', 'Mathematics', 'Progressions', 'Number sequences'),
    -- 和上面original_inserts里的行完全一致
    ('Algebra', 'Mathematics', 'Progressions', 'Geometric Progression')
  ON CONFLICT DO NOTHING 
  RETURNING ids_of_subject_section, name_of_subject, section, topic, subtopic
) ir
JOIN original_inserts oi
  ON LOWER(ir.name_of_subject) = LOWER(oi.name_of_subject)
  AND LOWER(ir.section) = LOWER(oi.section)
  AND LOWER(ir.topic) = LOWER(oi.topic)
  AND LOWER(ir.subtopic) = LOWER(oi.subtopic);

说明 #

• original_inserts 这个部分负责统计你原本计划插入的每个组合的出现次数，不管这些组合是否因为唯一索引冲突被跳过。
• 用LOWER()做匹配是因为你的原始插入里有Number Sequences和Number sequences（大小写差异），但数据库返回的是统一的小写结尾版本，这样能避免大小写不敏感导致的关联失败。
• 执行后你会得到正好是你想要的结果：ID 3对应12次，ID33对应4次，ID37对应2次。

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