PostgreSQL 批量插入&更新数据时报错（ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time）

在项目中使用 ON CONFLICT DO UPDATE SET 语法进行数据批量插入&更新，如果一条SQL语句中出现多条相同KEY的数据（冲突键，或冲突约束），会报错。

在SQL标准中，MERGE 也有同样的问题，因为一次请求中对行的处理，顺序是不固定的，数据库不知道应该以哪条为最后需要保留的数据，报错如下：

ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time  
Hint: Ensure that no rows proposed for insertion within the same command 
have duplicate constrained values.; 
bad SQL grammar []; nested exception is org.postgresql.util.PSQLException:
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
Hint: Ensure that no rows proposed for insertion within the same command have duplicate 
constrained values.

报错代码如下：

<insert id="batchDeviceInsert" parameterType="java.util.List">
    INSERT INTO t_cloud_device (
    device_guid,
    stage,
    device_id,
    chipid,
    first_activate_time,
    first_activate_ip,
    last_activate_ip,
    last_login_time,
    last_activate_time,
    product_type_code,
    activate_count,
    auth_soft_version,
    business_code,
    business_name,
    auth_info,
    contract_no,
    soc_name,
    status,
    product_category_id,
    source_type,
    is_delete,
    create_time,
    modify_time,
    business_id,
    province_id,
    city_id,
    barcode
    )
    VALUES
    <foreach collection="deviceBOList" item="item" index="index" separator=",">
        (
        #{item.deviceGuid},
        #{item.stage},
        #{item.deviceId},
        #{item.chipId},
        #{item.firstActivateTime},
        #{item.firstActivateIp},
        #{item.lastActivateIp},
        #{item.lastLoginTime},
        #{item.lastActivateTime},
        #{item.productTypeCode},
        #{item.activateCount},
        #{item.authSoftVersion},
        #{item.businessCode},
        #{item.businessName},
        #{item.authInfo},
        #{item.contractNo},
        #{item.socName},
        #{item.status},
        #{item.productCategoryId},
        #{item.sourceType},
        0,
        #{item.createTime},
        #{item.modifyTime},
        #{item.businessId},
        #{item.provinceId},
        #{item.cityId},
        #{item.barcode}
        )
    </foreach>
    ON CONFLICT(device_guid)
    DO UPDATE SET
    first_activate_time = EXCLUDED.first_activate_time,
    first_activate_ip = EXCLUDED.first_activate_ip,
    activate_count = EXCLUDED.activate_count,
    business_id = EXCLUDED.business_id,
    business_code = EXCLUDED.business_code,
    business_name = EXCLUDED.business_name,
    last_activate_ip = EXCLUDED.last_activate_ip,
    last_login_time = EXCLUDED.last_login_time,
    last_activate_time = EXCLUDED.last_activate_time,
    barcode = EXCLUDED.barcode,
    province_id = EXCLUDED.province_id,
    city_id = EXCLUDED.city_id
</insert>

PostgreSQL不处理这种错误，应该让用户自己来保障，不能在同一条SQL中出现多条相同KEY的数据。