postgresql 9.5版本之前实现upsert功能

最近有开发人员问，有没有办法实现在pg9.5版本之前实现upsert功能，现整理如下--创建测试表，注意此处先不要主键或唯一约束create table t2 (id int,name varchar(100));-- pg 在9.5之前实现不存在则插入-- 现在需要实现，当id字段的值存在时，则更新name字段的值，如果id字段的值不存在，则执行插入with upsert as (update t2 set name='rudy1' where id=5 returning *) insert into t2 select 5,'rudy' where not exists (select 1 from upsert where id=5);--注意使用此种方法并不能保证两个事务同时插入一条数据-- session1执行该语句，成功postgres=# begin;BEGINpostgres=# with upsert as (update t2 set name='rudy2' where id=5 returning *) insert into t2 select 5,'rudy' where not exists (select 1 from upsert where id=5);INSERT 0 1-- session2执行该语句，也成功postgres=# begin;BEGINpostgres=# with upsert as (update t2 set name='rudy2' where id=5 returning *) insert into t2 select 5,'rudy' where not exists (select 1 from upsert where id=5);INSERT 0 1--两者都提交后发现id=5的数据有两条postgres=# select * from t2; id | name ----+------  5 | rudy  5 | rudy--为了保证并发，此时可以给表加上主键或唯一键  postgres=# alter table t2 add primary key(id);ALTER TABLE   --此时session2再提交语句则会报错 postgres=# begin;BEGINpostgres=# with upsert as (update t2 set name='rudy3' where id=5 returning *) insert into t2 select 5,'rudy' where not exists (select 1 from upsert where id=5); ERROR:  duplicate key value violates unique constraint "t2_pkey"DETAIL:  Key (id)=(5) already exists.      --那有没有办法实现在表上没有主键或唯一约束时，也能保证并发呢？--有，此时需要使用pg_try_advisory_xact_lock（其是一个轻量级的锁，在事务回滚或提交后，会自动释放锁），但其接受的参数是整数，为了保证尽量唯一，可以使用md5函数  --借助于lock实现upsert,注意此sql对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会lock着记录，后来的session会waitwith w1 as(select ('x'||substr(md5('6'),1,16))::bit(64)::bigint as tra_id),upsert as (update t2 set name='rudy2' where id=6 returning *)insert into t2 select 6,'rudy' from w1 where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=6);--借助于lock实现upsert,注意此sql对于记录不存在，可以保证只有一个session插入数据，对于同一条数据更新，先来的session会更新数据，后来的session 失败with w1 as(select ('x'||substr(md5('6'),1,16))::bit(64)::bigint as tra_id),upsert as (update t2 set name='rudy2' from w1 where pg_try_advisory_xact_lock(tra_id) and id=6 returning *)insert into t2 select 6,'rudy' from w1 where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=6); --后记--为了保证性能，id字段最好有索引（但不一定是主键或唯一约束时）--如果校验字段是否字段不为id，把相应字段的替换掉id字段则可--由md5虚拟的tra_id并不保证一定是唯一的，但重复的概率极低-- 在pg9.5中可以直接使用upsert，注意此时要求表上有主键或唯一约束insert into t2 values(5,'rudy1') ON CONFLICT(id) do update set name=EXCLUDED.name ;--对于mysql可以使用insert into on duplicate key实现类似功能（其也要求有主键或唯一约束）在此不详细举例--对于oracle可以使用merge into,想想还是这个更强大，嘿嘿