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

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍如何基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
缓存 安全 程序员
【ARM学习】Cortex- A系列程序员学习指南
【ARM学习】Cortex- A系列程序员学习指南
931 0
|
Shell 测试技术 Linux
通过shell脚本进行linux服务器的CPU和内存压测
通过shell脚本进行linux服务器的CPU和内存压测
965 0
|
2月前
|
人工智能 监控 测试技术
AI系统如何做性能测试?
AI性能测试正从传统接口压测转向全链路容量工程:TTFT、TPOT、Token吞吐、KV缓存、Agent调用链等新指标成为关键。慢的根源常不在模型,而在检索、工具调用或调度排队。测试需分层压测,兼顾性能、质量与成本。
|
12天前
|
语音技术
阿里云百炼Token Plan个人版可以买一年,年付价格更划算,帮大家测试了!
阿里云Token Plan个人版年付更划算:Lite/Essential/Standard/Pro四档年费仅420/860/1510/5600元,较月付最高省42%。Standard版性价比最优,免费赠Harness权益(含AgentStudio工具及后付88折)。团队版仅支持月订。阿里云百炼Token Plan官网:https://t.aliyun.com/U/EsRjVx
131 0
|
6月前
|
存储 弹性计算 运维
阿里云服务器怎么买?四种主要方式详解+注意事项,新手购买参考教程
本文介绍了阿里云服务器的四大购买方式的适用场景与注意事项:自定义购买支持全参数精细配置,适合有技术基础的企业用户;快速购买通过预设模板简化流程,助力新手快速上云;活动购买提供低至38元/年的限时优惠,覆盖99计划、学生300元抵扣金、百炼先用后返等多重权益;云市场镜像购买提供预装环境的开箱即用方案,适合中小企业快速建站。
|
1月前
|
人工智能 编解码 自然语言处理
AI 视频制作:一套从零到成片的工作流(商汤 Seko、即梦 AI、Runway、Pika 等工具组合)
这套工作流的核心思路其实就一句:让 AI 承担大部分执行工作,把精力留给选题判断和内容审核。按这个流程跑 3~5 条之后,你会对"哪一步该让 AI 做、哪一步必须自己把关"有更具体的手感,单条制作时间也会自然往下走。
1187 0
|
10月前
|
机器学习/深度学习 供应链 监控
淘宝图片搜索API(taobao.item_search_img)
淘宝图片搜索API是阿里基于深度学习的视觉检索服务,支持以图搜同款/相似商品,毫秒级响应、高准确率。提供商品、交易、店铺等结构化数据,适配选品、同款监控、智能上架等场景,合规高效,助力电商数字化升级。(239字)
1194 7
|
11月前
|
存储 弹性计算 测试技术
5 款高性价比阿里云服务器测评:覆盖个人 / 企业全场景,省钱又实用
阿里云服务器持续推出高性价比配置,结合实例性能、价格优势与实际使用场景,筛选出 5 款热门爆品。本文从配置参数、核心优势、适用场景等维度展开测评,帮不同需求的用户精准选择,避开选购误区。
|
IDE 开发工具 开发者
python新手,关于模块化那些事
Python模块化编程是将程序拆分为多个功能模块的方法,有助于提升代码可读性、维护性和复用性。本文介绍了模块的定义与导入方式、包的结构与使用、模块搜索路径、命名规范、导入最佳实践及项目实战案例,帮助开发者构建清晰、专业的Python项目结构,提升开发效率与代码质量。
957 1