postgresql 9.5版本之前实现upsert功能

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 最近有开发人员问,有没有办法实现在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,想想还是这个更强大,嘿嘿
  

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
存储 关系型数据库 MySQL
PolarDB优势功能
PolarDB优势功能
|
2月前
|
关系型数据库 MySQL
若依框架----如何降低mysql驱动版本?
若依框架----如何降低mysql驱动版本?
35 3
|
13天前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
28 7
|
14天前
|
关系型数据库 MySQL 数据安全/隐私保护
【极光系列】Windows安装Mysql8.0版本
【极光系列】Windows安装Mysql8.0版本
23 1
|
27天前
|
关系型数据库 MySQL 数据安全/隐私保护
MySQL8.0绿色版本——最纯净的安装体验(免去卸载不掉只能重做系统的‘囧‘境)
MySQL8.0绿色版本——最纯净的安装体验(免去卸载不掉只能重做系统的‘囧‘境)
58 0
|
1月前
|
JSON 关系型数据库 MySQL
穿越MySQL版本时光:5.7和8.0的差异全揭秘
穿越MySQL版本时光:5.7和8.0的差异全揭秘
63 0
|
1月前
|
关系型数据库 Linux Shell
Centos系统上安装PostgreSQL和常用PostgreSQL功能
Centos系统上安装PostgreSQL和常用PostgreSQL功能
|
2月前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL企业版与标准版功能对比:如何选择适合您的版本?
随着数字化时代的到来,企业对于数据处理的需求越来越高,而数据库作为数据处理的核心,其性能和成本成为了企业关注的焦点。阿里云全新推出的PolarDB MySQL企业版和标准版,以全新的架构和优化,为企业提供了高性能、低成本的数据库解决方案。但在功能上,这两个版本有很多差异,我们该如何选择呢?
21 2
|
2月前
|
关系型数据库 MySQL 数据库
MySQL安装配置初始化 Windows10安装8.0.23版本
MySQL是应用最广泛、普及度最高的开源关系型数据库。
26 0
|
2月前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能【1月更文挑战第13天】【1月更文挑战第65篇】
17 2

热门文章

最新文章