PostgreSQL upset解决在插入过程中重复数据冲突

关于重复行问题：

　　在SQL Server中则可以自动排出重复行，不需要处理。在Oracle中经常遇到upsert语法，来排出冲突行。在PostgreSQL中，也需要手动排出重复行，否则会爆出错误，upsert用法如下：

　　

    WITH upsert AS (

         UPDATE TEST AS a

         SET

         updatetime=b.updatetime, msec=b.msec

         FROM TEMP_WRITEEVTUPDATETMSTAMP AS b

         WHERE a.deviceid=b.deviceid AND a.eventtype=b.eventtype

         RETURNING a.deviceid,a.eventtype

     )

     INSERT INTO TEST

     SELECT b.deviceid, b.eventtype, b.updatetime,b.msec

     FROM TEMP_WRITEEVTUPDATETMSTAMP b

     WHERE NOT EXISTS (SELECT 1 FROM upsert as a WHERE a.deviceid=b.deviceid AND a.eventtype=b.eventtype);    

　　经过测试，发现这种方法效率很低，于是将upsert过程拆分为两个过程，先insert不重复的数据，然后再执行update：

　　

    INSERT INTO TEST

     Select b.*

     from TEMP_WRITEEVTUPDATETMSTAMP b, (select deviceid, eventtype, max(ctid) max_ctid from TEMP_WRITEEVTUPDATETMSTAMP group by deviceid, eventtype) c

     where (b.deviceid = c.deviceid and b.eventtype = c.eventtype and b.ctid = c.max_ctid) and NOT EXISTS (SELECT 1 FROM TEST as a WHERE a.deviceid=b.deviceid AND a.eventtype=b.even

     UPDATE TEST AS a

     SET

     updatetime=b.updatetime, msec=b.msec

     FROM TEMP_WRITEEVTUPDATETMSTAMP AS b

     WHERE a.deviceid=b.deviceid AND a.eventtype=b.eventtype;

当时在存储过程中大量使用该方法来去除重复数据的插入，但是最后发现还有更好的方法来处理，upsert：

　　　INSERT INTO TEST

     select * from TEMP_WRITEEVTUPDATETMSTAMP on conflict pk_test_deviceid_eventtype do update set updatetime = excluded.updatetime, msec = excluded.msec;