PostgreSQL Upsert中如何指定当前更新列？解决子查询多行问题

你的问题核心在于DO UPDATE段的子查询没有关联到当前正在处理的冲突行，导致子查询返回了所有符合条件的分组结果（多行），而SET子句要求每个赋值必须是单个值，因此触发报错。

其实完全不需要重复编写聚合子查询——INSERT的SELECT部分已经算出了每个(nm, date)分组对应的maxOdometer、kmDifferencePerDay等值，PostgreSQL提供的EXCLUDED关键字可以直接引用INSERT语句中准备插入的行的列值，既高效又能解决多行结果的问题。

修改后的完整语句如下：

INSERT INTO tbl_ws_gps_history(nm, date, "maxOdometer", "minOdometer", "kmDifferencePerDay", "currentOdometer")
SELECT  nm, fecha,
        MAX("odommetro"), 
        MIN("odommetro"), 
        (MAX("odommetro") - MIN("odommetro")),
        MAX("odommetro")
FROM tbl_admon_gps_data
WHERE fecha = CURRENT_DATE
GROUP BY nm, fecha
ORDER BY nm
ON CONFLICT (nm, date) DO UPDATE
SET "maxOdometer" = EXCLUDED."maxOdometer",
    "kmDifferencePerDay" = EXCLUDED."kmDifferencePerDay",
    "currentOdometer" = EXCLUDED."currentOdometer";

关键说明： #

• EXCLUDED代表原本要插入的那一行数据，包含了SELECT子句中计算好的所有列值，刚好匹配当前冲突的(nm, date)分组。
• 这种写法避免了重复执行聚合查询，大幅提升性能，同时彻底解决子查询返回多行的问题。
• 如果需要同步更新minOdometer，只需在SET中添加"minOdometer" = EXCLUDED."minOdometer"即可。

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