如何在PostgreSQL的cases表中添加聚合求和生成的新行？

首先，咱们来拆解你遇到的问题，然后一步步解决：

你的原INSERT语句的核心问题 #

1. 列顺序不匹配：你的cases表（除了自动生成的id）的列顺序是disease, country, year, number, rate，但你原查询把聚合数值放在最前面，后面才是country和year，这会导致插入时列值对应错误，甚至触发约束问题。
2. 错误处理NULL/NaN：PostgreSQL里，数值类型的空值是NULL，不是字符串'NaN'——用number <> 'NaN'会因为类型不匹配导致判断失效，而且你的number列是NOT NULL约束，当聚合结果为NULL时（比如某组所有值都是空），就会触发你看到的非空约束错误。
3. 聚合行的结构错误：你试图在一行里生成多个聚合类型（DTP、MMR、Polio）的数值，但需求是每个聚合类型单独生成一行，所以需要用UNION ALL把三个独立的聚合查询合并。

正确的解决方案 #

下面的语句会按country和year分组，分别生成DTP、MMR、Polio的聚合行，同时处理空值确保符合number列的非空约束：

INSERT INTO cases (disease, country, year, number, rate)
-- 生成DTP聚合行
SELECT
  'DTP' AS disease,
  country,
  year,
  -- 用COALESCE把SUM的NULL结果转为0.00，满足number非空约束
  COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') THEN number END), 0.00) AS number,
  COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') THEN rate END), 0.00) AS rate
FROM cases
GROUP BY country, year

UNION ALL

-- 生成MMR聚合行
SELECT
  'MMR' AS disease,
  country,
  year,
  COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') THEN number END), 0.00) AS number,
  COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') THEN rate END), 0.00) AS rate
FROM cases
GROUP BY country, year

UNION ALL

-- 生成Polio聚合行
SELECT
  'Polio' AS disease,
  country,
  year,
  COALESCE(SUM(CASE WHEN disease = 'Polio' THEN number END), 0.00) AS number,
  COALESCE(SUM(CASE WHEN disease = 'Polio' THEN rate END), 0.00) AS rate
FROM cases
GROUP BY country, year;

额外优化：避免重复插入 #

因为你的表有UNIQUE (disease, country, year, number)约束，如果重复运行这个语句会触发唯一约束错误。可以用ON CONFLICT子句跳过已存在的行：

INSERT INTO cases (disease, country, year, number, rate)
SELECT * FROM (
  SELECT
    'DTP' AS disease,
    country,
    year,
    COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') THEN number END), 0.00) AS number,
    COALESCE(SUM(CASE WHEN disease IN ('Diphtheria', 'Tetanus', 'Pertussis') THEN rate END), 0.00) AS rate
  FROM cases
  GROUP BY country, year

  UNION ALL

  SELECT
    'MMR' AS disease,
    country,
    year,
    COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') THEN number END), 0.00) AS number,
    COALESCE(SUM(CASE WHEN disease IN ('Measles', 'Mumps', 'Rubella') THEN rate END), 0.00) AS rate
  FROM cases
  GROUP BY country, year

  UNION ALL

  SELECT
    'Polio' AS disease,
    country,
    year,
    COALESCE(SUM(CASE WHEN disease = 'Polio' THEN number END), 0.00) AS number,
    COALESCE(SUM(CASE WHEN disease = 'Polio' THEN rate END), 0.00) AS rate
  FROM cases
  GROUP BY country, year
) AS aggregated_rows
ON CONFLICT (disease, country, year, number) DO NOTHING;

为什么这个能解决你的错误？ #

你遇到的null values in the "number" column violate the non-null constraint错误，是因为当某个国家/年份的对应疾病的number全为空时，SUM的结果是NULL，而number列不允许空值。用COALESCE(..., 0.00)把NULL替换成0.00，就能满足约束要求。

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