How can I indicate the "current" column in an upsert in PostgreSQL?-CodePudding

I have the following upsert with which I have problems because the subqueries give me more than one result. The problem is that I don't know how to indicate in the upsert to compare the value of the column that is currently being updated. The problem is after the DO UPDATE.

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"           = (SELECT MAX(d."odommetro") FROM tbl_admon_gps_data d, tbl_ws_gps_history h WHERE d.fecha = CURRENT_DATE AND d.nm = h.nm AND d.fecha = h.date GROUP BY d.nm, d.fecha ORDER BY d.nm),
    "kmDifferencePerDay"    = (SELECT (MAX("odommetro") - MIN("odommetro")) FROM tbl_admon_gps_data WHERE fecha = CURRENT_DATE GROUP BY nm, fecha ORDER BY nm),
    "currentOdometer"       = (SELECT MAX("odommetro") FROM tbl_admon_gps_data WHERE fecha = CURRENT_DATE GROUP BY nm, fecha ORDER BY nm);

CodePudding user response：

I think you are looking for the excluded record that represents the row that would have been inserted:

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";