PostgreSQL upsert功能(insert on conflict do)的用法

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

PostgreSQL , upsert , insert on conflict do


背景

PostgreSQL 9.5 引入了一项新功能,UPSERT(insert on conflict do),当插入遇到约束错误时,直接返回,或者改为执行UPDATE。

语法如下

Command:     INSERT  
Description: create new rows in a table  
Syntax:  
[ WITH [ RECURSIVE ] with_query [, ...] ]  
INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ]  
    { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }  
    [ ON CONFLICT [ conflict_target ] conflict_action ]  
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]  
  
where conflict_target can be one of:  
  
    ( { index_column_name | ( index_expression ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ]  
    ON CONSTRAINT constraint_name  
  
and conflict_action is one of:  
  
    DO NOTHING  
    DO UPDATE SET { column_name = { expression | DEFAULT } |  
                    ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) |  
                    ( column_name [, ...] ) = ( sub-SELECT )  
                  } [, ...]  
              [ WHERE condition ]  

PostgreSQL 9.5以前的版本,可以通过函数,或者with语法来实现UPSERT类似的功能。

9.5+ UPSERT用法举例

创建一张测试表,其中一个字段为唯一键或者主键。

create table test(id int primary key, info text, crt_time timestamp);  

1. 不存在则插入,存在则更新

test03=# insert into test values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;  
INSERT 0 1  
  
test03=# select * from test;  
 id | info |          crt_time            
----+------+----------------------------  
  1 | test | 2017-04-24 15:27:25.393948  
(1 row)  
  
test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time;  
INSERT 0 1  
  
test03=# select * from test;  
 id |     info     |          crt_time            
----+--------------+----------------------------  
  1 | hello digoal | 2017-04-24 15:27:39.140877  
(1 row)  

2. 不存在则插入,存在则直接返回(不做任何处理)

test03=# insert into test values (1,'hello digoal',now()) on conflict (id) do nothing;  
INSERT 0 0  
test03=# insert into test values (1,'pu',now()) on conflict (id) do nothing;  
INSERT 0 0  
test03=# insert into test values (2,'pu',now()) on conflict (id) do nothing;  
INSERT 0 1  
test03=# select * from test;  
 id |     info     |          crt_time            
----+--------------+----------------------------  
  1 | hello digoal | 2017-04-24 15:27:39.140877  
  2 | pu           | 2017-04-24 15:28:20.37392  
(2 rows)  

9.5- UPSERT用法举例

用户可以根据实际需求,使用不同的方法

1. 函数

test03=# create or replace function f_upsert(int,text,timestamp) returns void as $$  
declare  
  res int;  
begin  
  update test set info=$2,crt_time=$3 where id=$1;  
  if not found then  
    insert into test (id,info,crt_time) values ($1,$2,$3);  
  end if;  
  exception when others then  
    return;  
end;  
$$ language plpgsql strict;  
CREATE FUNCTION  
  
test03=# select f_upsert(1,'digoal',now()::timestamp);  
 f_upsert   
----------  
   
(1 row)  
  
test03=# select * from test;  
 id |  info  |          crt_time            
----+--------+----------------------------  
  2 | pu     | 2017-04-24 15:28:20.37392  
  1 | digoal | 2017-04-24 15:31:29.254325  
(2 rows)  
  
test03=# select f_upsert(1,'digoal001',now()::timestamp);  
 f_upsert   
----------  
   
(1 row)  
  
test03=# select * from test;  
 id |   info    |         crt_time            
----+-----------+---------------------------  
  2 | pu        | 2017-04-24 15:28:20.37392  
  1 | digoal001 | 2017-04-24 15:31:38.0529  
(2 rows)  
  
test03=# select f_upsert(3,'hello',now()::timestamp);  
 f_upsert   
----------  
   
(1 row)  
  
test03=# select * from test;  
 id |   info    |         crt_time            
----+-----------+---------------------------  
  2 | pu        | 2017-04-24 15:28:20.37392  
  1 | digoal001 | 2017-04-24 15:31:38.0529  
  3 | hello     | 2017-04-24 15:31:49.14291  
(3 rows)  

2. WITH语法,用法1

create table test(id int primary key, info text, crt_time timestamp);  

存在则更新,不存在则插入。

with upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *) insert into test select $id,$info,$crt_time where not exists (select 1 from upsert where id=$id);    

替换变量,进行测试

with upsert as (update test set info='test',crt_time=now() where id=1 returning *) insert into test select 1,'test',now() where not exists (select 1 from upsert where id=1);    

同时插入一条不存在的值,只有一个会话成功,另一个会话会报PK约束错误。

3. WITH语法,用法2

即使表没有PK或者唯一约束,也能保证并发。

create table test(id int, info text, crt_time timestamp);  

3.1 对于记录不存在,可以保证只有一个session插入数据,对于同一条数据更新,先来的session会lock着记录,后来的session会wait。

with     
  w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info=$info,crt_time=$crt_time where id=$id returning *)    
  insert into test select $id, $info, $crt_time from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);    

替换变量,进行测试

with     
  w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='digoal0123',crt_time=now() where id=1 returning *)    
  insert into test select 1, 'digoal0123', now() from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
  
INSERT 0 0  
  
test03=# select * from test;  
 id |    info    |         crt_time            
----+------------+---------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  1 | digoal0123 | 2017-04-24 15:31:38.0529  
(3 rows)  
  
with     
  w1 as(select ('x'||substr(md5('4'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='digoal0123',crt_time=now() where id=4 returning *)    
  insert into test select 4, 'digoal0123', now() from w1     
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=4);    
  
INSERT 0 1  
  
test03=# select * from test;  
 id |    info    |          crt_time            
----+------------+----------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  1 | digoal0123 | 2017-04-24 15:31:38.0529  
  4 | digoal0123 | 2017-04-24 15:38:39.801908  
(4 rows)  

3.2 对于记录不存在,可以保证只有一个session插入数据,对于同一条数据更新,先来的session会更新数据,后来的session不等待,直接失败。

with w1 as(select ('x'||substr(md5('$id'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info=$info,crt_time=$crt_time from w1 where pg_try_advisory_xact_lock(tra_id) and id=$id returning *)    
  insert into test select $id,$info,$crt_time from w1   
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=$id);     

替换变量,进行测试

with w1 as(select ('x'||substr(md5('1'),1,16))::bit(64)::bigint as tra_id),    
  upsert as (update test set info='test',crt_time=now() from w1 where pg_try_advisory_xact_lock(tra_id) and id=1 returning *)    
  insert into test select 1,'test',now() from w1   
    where pg_try_advisory_xact_lock(tra_id) and not exists (select 1 from upsert where id=1);    
  
INSERT 0 0  
  
test03=# select * from test;  
 id |    info    |          crt_time            
----+------------+----------------------------  
  2 | pu         | 2017-04-24 15:28:20.37392  
  3 | hello      | 2017-04-24 15:31:49.14291  
  4 | digoal0123 | 2017-04-24 15:42:50.912887  
  1 | test       | 2017-04-24 15:44:44.245167  
(4 rows)  
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
7天前
|
SQL Oracle 关系型数据库
实时计算 Flink版操作报错之往GREENPLUM 6 写数据,用postgresql-42.2.9.jar 报 ON CONFLICT (uuid) DO UPDATE SET 语法有问题。怎么解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
15天前
|
关系型数据库 Serverless 分布式数据库
【公测】PolarDB PostgreSQL版Serverless功能免费使用​!
【公测】PolarDB PostgreSQL版Serverless功能免费使用​,公测于2024年3月28日开始,持续三个月,公测期间可以免费使用!
|
15天前
|
SQL 关系型数据库 MySQL
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
MySQL技能完整学习列表3、SQL语言基础——1、SQL(Structured Query Language)简介——2、基本SQL语句:SELECT、INSERT、UPDATE、DELETE
58 0
|
15天前
|
关系型数据库 Serverless 分布式数据库
PolarDB PostgreSQL版Serverless功能上线公测啦,公测期间免费使用!
Serverless数据库能够使得数据库集群资源随客户业务负载动态弹性扩缩,将客户从复杂的业务资源评估和运维工作中解放出来。PolarDB PostgreSQL版 Serverless提供了CPU、内存、存储、网络资源的实时弹性能力,构建计算与存储分离架构下的 PolarDB PostgreSQL版产品新形态。
|
3天前
|
关系型数据库 MySQL 分布式数据库
【PolarDB开源】PolarDB开源社区动态:最新版本功能亮点与更新解读
【5月更文挑战第27天】PolarDB最新版发布,提升内核稳定性和性能,采用X-Paxos增强一致性与容灾能力。新版本支持分布式CDC升级,实现数据实时同步和离线分析。引入PITR功能,确保故障后快速恢复至指定时间点。提供MySQL一键导入,降低迁移成本。示例代码展示从MySQL到PolarDB-X的数据导入。随着技术发展,PolarDB将在企业数据处理中扮演更重要角色。
148 0
|
15天前
|
关系型数据库 MySQL 分布式数据库
PolarDB MySQL版集群版本支持库表恢复功能的版本要求是什么?
【5月更文挑战第13天】PolarDB MySQL版集群版本支持库表恢复功能的版本要求是什么?
14 0
|
15天前
|
SQL 运维 关系型数据库
PolarDB产品使用合集之PolarDB 2.3.0 版本的 CDC 功能支持 Polardb-X 到 Polardb-X 的数据同步吗
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
15天前
|
关系型数据库 MySQL 分布式数据库
PolarDB产品使用合集之PolarDB MySQL标准版中带有分区功能吗
PolarDB产品使用合集涵盖了从创建与管理、数据管理、性能优化与诊断、安全与合规到生态与集成、运维与支持等全方位的功能和服务,旨在帮助企业轻松构建高可用、高性能且易于管理的数据库环境,满足不同业务场景的需求。用户可以通过阿里云控制台、API、SDK等方式便捷地使用这些功能,实现数据库的高效运维与持续优化。
|
15天前
|
SQL 关系型数据库 分布式数据库
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
在PolarDB for PostgreSQL中,你可以使用LIKE运算符来实现类似的查询功能,而不是使用IF函数
54 7
|
15天前
|
关系型数据库 Linux Shell
Centos系统上安装PostgreSQL和常用PostgreSQL功能
Centos系统上安装PostgreSQL和常用PostgreSQL功能

相关产品

  • 云原生数据库 PolarDB