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;
BEGIN
postgres=# 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;
BEGIN
postgres=# 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;
BEGIN
postgres=# 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会wait
with 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,想想还是这个更强大，嘿嘿