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

开发者社区> rudy_gao> 正文

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,想想还是这个更强大,嘿嘿
  

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

相关文章
SAP Hybris Commerce 新版本功能解析
  SAP Hybris Commerce 新版本功能解析   http://tech.
1291 0
数据库云HBase 版本spark服务支持D1机型
信息摘要: 数据库云HBase 版本spark服务支持D1机型,适合起步超过20T数据库的大客户,每GB存储单价最低。适用客户: 大企业版本/规格功能: spark支持D1机型产品文档: 数据库云HBase 版本spark服务支持D1机型,具体spark服务参考https://help.
480 0
soufun使用次数统计,搜房网房源自动刷新程序 -- 测试版本,Dev.QQ 1205878060
soufun使用次数统计,搜房网房源自动刷新程序 -- 测试版本,Dev.QQ 1205878060  
611 0
Consul Config 使用Git做版本控制的实现
Spring Cloud Config 原理 我们通过git 把配置文件推送到远程仓库做版本控制,当版本发生变化的时候,远程仓库通过webhook机制推送消息给 Config Server,Config Server 将修改通知发送到消息总线,然后所有的Config Client 进行配置刷新。
1866 0
Dataphin新版本功能解读系列之-V2.5
作者:陈梦婷 更多内容详见数据中台官网 https://dp.alibaba.com 作为今年最值得期待的版本,Dataphin V2.5 终于如期和大家见面啦!此版本不仅在功能完备性上有所提升,支持多种研发模式,数据萃取、数据服务功能升级,在产品体验上也进行了较大程度的优化,为您带来更流畅便捷的操作体验。
4475 0
9.28直播预告|AnalyticDB for PostgreSQL功能发布 - 外表联邦分析&列存引擎增强
本次分享主要介绍云原生数据仓库ADB PG公共云近期发布的两项重要功能,外表联邦分析和列存引擎增强的技术解析,和最佳使用实践,欢迎大家观看直播。
1044 0
Hanlp1.7版本的新增功能一览
Hanlp1.7版本在去年下半年的时候就随大快的DKH1.6版本同时发布了,截至目前1.7大版本也更新到了1.7.1了。本篇分别就1.7.0和1.7.1中新增的功能做一个简单的汇总介绍。
2033 0
阿里云数据库推出SQL Server 2016 单机基础版本
2017年9月,云数据库推出SQL Server 2016版本,本文是2016单机基础版本的产品形态说明。
4455 0
+关注
487
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载