postgresql 9.5版本之前实现upsert功能-阿里云开发者社区

开发者社区> 数据库> 正文

postgresql 9.5版本之前实现upsert功能

简介: 最近有开发人员问,有没有办法实现在pg9.5版本之前实现upsert功能,现整理如下 --创建测试表,注意此处先不要主键或唯一约束 create table t2 (id int,name varchar(100)); -- pg 在9.
最近有开发人员问,有没有办法实现在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,想想还是这个更强大,嘿嘿
  

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章