PostgreSQL Upsert因夏令时（DST）变更报错的解决方法

错误根源 #

你遇到的ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time错误，核心原因是夏令时切换导致同一INSERT命令中出现了重复的约束键值：
PostgreSQL的timestamp with time zone（timestamptz）类型会将所有时间转换为UTC统一存储。在2015年3月29日欧洲夏令时切换时段，2015-03-29 03:00:00+02:00和2015-03-29 04:00:00+03:00实际上对应同一个UTC时间（均为2015-03-29 01:00:00 UTC）。加上两条记录的sensor_id都是20，这就违反了(sensor_id, timestamp)的唯一约束，且因为是同一条INSERT命令内的重复，触发了该错误。

另外注意你的SQL语句中有一条记录存在语法错误：

('20', timestamp with time zone '2015-03-29 03:00:00+02:00', 0.两cl Forever(Tcl, ThingsSand�显示 stops,Numberady, 0.0, '0', 7200),

value字段的乱码需要先修正，否则会直接导致SQL执行失败。

解决方案 #

方案1：修正输入数据的时间值 #

夏令时切换时，部分本地时间是不存在的（比如欧洲时区在2015-03-29的02:00-03:00会直接跳转到03:00+03），你输入的2015-03-29 03:00:00+02:00属于无效的本地时间，PostgreSQL会自动将其映射到夏令时后的对应时间，导致和最后一条记录冲突。

直接修正这条时间为正确的夏令时格式即可：

('20', timestamp with time zone '2015-03-29 03:00:00+03:00', 0.0, '0', 10800),

方案2：修改约束规则，区分本地时间与时区 #

如果业务上需要将同一UTC时间但不同时区/偏移的记录视为不同条目，可以调整表的唯一约束，加入tz_offset字段：

1. 先删除原唯一约束：

ALTER TABLE data_sensordata DROP CONSTRAINT IF EXISTS data_sensordata_sensor_id_timestamp_key;

2. 添加包含时区偏移的新约束：

ALTER TABLE data_sensordata ADD CONSTRAINT data_sensordata_sensor_id_timestamp_tz_offset_key UNIQUE (sensor_id, timestamp, tz_offset);

3. 对应的Upsert语句修改冲突目标：

INSERT INTO data_sensordata (sensor_id, timestamp, value, flag, tz_offset)
VALUES
    -- 所有记录保持原格式，修正那条乱码记录后
    ('20', timestamp with time zone '2015-03-29 00:15:00+02:00', 0.0, '0', 7200),
    -- ... 其他记录 ...
    ('20', timestamp with time zone '2015-03-29 03:00:00+02:00', 0.0, '0', 7200),
    ('20', timestamp with time zone '2015-03-29 04:00:00+03:00', 0.0, '0', 10800)
ON CONFLICT (sensor_id, timestamp, tz_offset)
DO UPDATE SET
    value = EXCLUDED.value,
    flag  = EXCLUDED.flag;

方案3：在插入前对数据去重 #

如果无法修改表结构，且输入数据中必然存在这种UTC重复的情况，可以通过CTE先对输入数据去重，只保留需要的条目（比如优先保留夏令时后的记录）：

WITH input_data AS (
    SELECT * FROM (VALUES
        ('20', timestamp with time zone '2015-03-29 00:15:00+02:00', 0.0, '0', 7200),
        ('20', timestamp with time zone '2015-03-29 00:30:00+02:00', 0.0, '0', 7200),
        -- ... 其他记录，修正乱码后 ...
        ('20', timestamp with time zone '2015-03-29 03:00:00+02:00', 0.0, '0', 7200),
        ('20', timestamp with time zone '2015-03-29 04:00:00+03:00', 0.0, '0', 10800)
    ) AS t(sensor_id, timestamp, value, flag, tz_offset)
), deduplicated AS (
    SELECT DISTINCT ON (sensor_id, timestamp) *
    FROM input_data
    ORDER BY sensor_id, timestamp, tz_offset DESC -- 按时区偏移降序，保留夏令时后的记录
)
INSERT INTO data_sensordata (sensor_id, timestamp, value, flag, tz_offset)
SELECT sensor_id, timestamp, value, flag, tz_offset
FROM deduplicated
ON CONFLICT (sensor_id, timestamp)
DO UPDATE SET
    value = EXCLUDED.value,
    flag  = EXCLUDED.flag;

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