postgresql 9.5版本之前实现upsert功能_pg9.5以前版本实现upsert-优快云博客

本文链接：https://blog.youkuaiyun.com/rudygao/article/details/51281719

本文介绍了在PostgreSQL 9.5版本之前如何通过CTE和事务处理来模拟UPSERT功能，确保并发场景下数据的一致性。通过创建测试表并使用pg_try_advisory_xact_lock()函数，展示了两种避免数据冲突的方法，并提到了在9.5版本后可用的ON CONFLICT更新语法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

最近有开发人员问，有没有办法实现在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);