PostgreSQL：利用插入返回ID插入另一表及冲突更新报错排查

问题概述 #

需要实现：当mis_doctor_stats表中存在唯一键(doctor_id, year, month)重复的记录时，将现有sum字段值与输入的sum值累加。执行以下SQL时触发错误：FROM expected, got ';'

尝试的SQL语句：

with rows as (
    insert into mis_doctor_stats (doctor_id, year, month, sum)
        values (1, 2025, 3, 100),
               (1, 2021, 4, 6)
        on conflict (doctor_id, year, month) do update set sum = mis_doctor_stats.sum + excluded.sum
        returning id)
insert
into mis_doctor_stats_files(file_id, stats_id)
select 4, rows.id
from rows;

可能原因与解决方法 #

1. 数据库不支持CTE中嵌套DML操作（如MySQL） #

MySQL 8.0虽支持CTE，但不允许在CTE内使用INSERT/UPDATE/DELETE这类写操作，这会直接触发语法错误。需拆分语句执行：

步骤1：执行插入或更新操作

用MySQL的ON DUPLICATE KEY UPDATE替代PostgreSQL风格的ON CONFLICT：

insert into mis_doctor_stats (doctor_id, year, month, sum)
values (1, 2025, 3, 100),
       (1, 2021, 4, 6)
on duplicate key update sum = mis_doctor_stats.sum + values(sum);

步骤2：关联插入到mis_doctor_stats_files

查询目标记录的ID，批量插入关联表：

insert into mis_doctor_stats_files(file_id, stats_id)
select 4, id
from mis_doctor_stats
where (doctor_id, year, month) in ((1,2025,3), (1,2021,4));

2. 关键字冲突或版本兼容问题（如PostgreSQL） #

sum是SQL保留关键字，直接作为字段名可能引发解析错误，建议用双引号包裹；同时确保PostgreSQL版本在9.5及以上（支持ON CONFLICT）：

with rows as (
    insert into mis_doctor_stats (doctor_id, year, month, "sum")
        values (1, 2025, 3, 100),
               (1, 2021, 4, 6)
        on conflict (doctor_id, year, month) do update set "sum" = mis_doctor_stats."sum" + excluded."sum"
        returning id)
insert into mis_doctor_stats_files(file_id, stats_id)
select 4, rows.id
from rows;

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