PostgreSQL merge insert(upsert/insert into on conflict) 如何区分数据是INSERT还是UPDATE

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介: 标签PostgreSQL , merge insert , upsert , insert into on conflict , 区分 insert update , xmin , xmax背景使用insert into on conflict update语法,可以支持UPSERT的功能,但是到底这条SQL是插入的还是更新的呢?如何判断通过xmax字段的值是否不为0,可以判断,如果是UPDATE,XMAX里面会填充更新事务号。

标签

PostgreSQL , merge insert , upsert , insert into on conflict , 区分 insert update , xmin , xmax


背景

使用insert into on conflict update语法,可以支持UPSERT的功能,但是到底这条SQL是插入的还是更新的呢?如何判断

通过xmax字段的值是否不为0,可以判断,如果是UPDATE,XMAX里面会填充更新事务号。

注意直接用UPDATE语句更新的话,XMAX会写入0,因为是新版本,而老版本上XMAX会填入更新事务号。

例子

1 insert on conflict

postgres=# create table t(id int primary key, info text, crt_time timestamp);  
CREATE TABLE  
postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;  
 xmax   
------  
    0  
(1 row)  
  
INSERT 0 1  
postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;  
   xmax      
-----------  
 160369640  
(1 row)  
  
INSERT 0 1  
postgres=# select xmin,xmax,* from t;  
   xmin    |   xmax    | id | info |          crt_time            
-----------+-----------+----+------+----------------------------  
 160369640 | 160369640 |  1 | test | 2018-10-17 12:09:38.760926  
(1 row)  
  
postgres=# insert into t values (1,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;  
   xmax      
-----------  
 160369641  
(1 row)  
  
INSERT 0 1  
postgres=# select xmin,xmax,* from t;  
   xmin    |   xmax    | id | info |          crt_time            
-----------+-----------+----+------+----------------------------  
 160369641 | 160369641 |  1 | test | 2018-10-17 12:10:11.738691  
(1 row)  
  
postgres=# insert into t values (2,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning xmax;  
 xmax   
------  
    0  
(1 row)  
  
INSERT 0 1  
postgres=# select xmin,xmax,* from t;  
   xmin    |   xmax    | id | info |          crt_time            
-----------+-----------+----+------+----------------------------  
 160369641 | 160369641 |  1 | test | 2018-10-17 12:10:11.738691  
 160369642 |         0 |  2 | test | 2018-10-17 12:10:24.758745  
(2 rows)  
  
postgres=# select ctid,xmin,xmax,* from t;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,3) | 160369641 | 160369641 |  1 | test | 2018-10-17 12:10:11.738691  
 (0,4) | 160369642 |         0 |  2 | test | 2018-10-17 12:10:24.758745  
(2 rows)  
  
postgres=# insert into t values (2,'test',now()) on conflict (id) do update set info=excluded.info,crt_time=excluded.crt_time returning ctid,xmin,xmax,*;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,5) | 160369643 | 160369643 |  2 | test | 2018-10-17 12:10:45.951351  
(1 row)  
  
INSERT 0 1  
postgres=# select ctid,xmin,xmax,* from t;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,3) | 160369641 | 160369641 |  1 | test | 2018-10-17 12:10:11.738691  
 (0,5) | 160369643 | 160369643 |  2 | test | 2018-10-17 12:10:45.951351  
(2 rows)  

2 直接update

postgres=# update t set info='a' returning xmin,xmax,ctid,*;  
   xmin    | xmax | ctid  | id | info |          crt_time            
-----------+------+-------+----+------+----------------------------  
 160369644 |    0 | (0,6) |  1 | a    | 2018-10-17 12:10:11.738691  
 160369644 |    0 | (0,7) |  2 | a    | 2018-10-17 12:10:45.951351  
(2 rows)  
  
UPDATE 2  

3 update 回滚

postgres=# begin;  
BEGIN  
postgres=# update t set info='a' returning xmin,xmax,ctid,*;  
   xmin    | xmax | ctid  | id | info |          crt_time            
-----------+------+-------+----+------+----------------------------  
 160369645 |    0 | (0,8) |  1 | a    | 2018-10-17 12:10:11.738691  
 160369645 |    0 | (0,9) |  2 | a    | 2018-10-17 12:10:45.951351  
(2 rows)  
  
UPDATE 2  
postgres=# rollback;  
ROLLBACK  
postgres=# select ctid,xmin,xmax,* from t;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,6) | 160369644 | 160369645 |  1 | a    | 2018-10-17 12:10:11.738691  
 (0,7) | 160369644 | 160369645 |  2 | a    | 2018-10-17 12:10:45.951351  
(2 rows)  

4 delete 回滚

postgres=# begin;  
BEGIN  
postgres=# delete from t returning ctid,xmin,xmax,*;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,6) | 160369644 | 160369646 |  1 | a    | 2018-10-17 12:10:11.738691  
 (0,7) | 160369644 | 160369646 |  2 | a    | 2018-10-17 12:10:45.951351  
(2 rows)  
  
DELETE 2  
postgres=# rollback;  
ROLLBACK  
postgres=# select ctid,xmin,xmax,* from t;  
 ctid  |   xmin    |   xmax    | id | info |          crt_time            
-------+-----------+-----------+----+------+----------------------------  
 (0,6) | 160369644 | 160369646 |  1 | a    | 2018-10-17 12:10:11.738691  
 (0,7) | 160369644 | 160369646 |  2 | a    | 2018-10-17 12:10:45.951351  
(2 rows)  

小结

1、insert into on conflict do update,返回xmax不等于0,表示update,等于0表示insert。

2、直接update,并提交,提交的记录上xmax为0。

3、直接update,并回滚,老版本上的XMAX不为0,表示更新该行的事务号。

4、直接DELETE,并回滚,老版本上的XMAX不为0,表示删除该行的事务号。

ctid表示行号, xmin表示INSERT该记录的事务号,xmax表示删除该记录(update实际上是删除老版本新增新版本,所以老版本上xmax有值)的事务号。

参考

《Greenplum & PostgreSQL UPSERT udf 实现 - 2 batch批量模式》

《Greenplum & PostgreSQL UPSERT udf 实现 - 1 单行模式》

《PostgreSQL 多重含义数组检索与条件过滤 (标签1:属性, 标签n:属性) - 包括UPSERT操作如何修改数组、追加数组元素》

《HTAP数据库 PostgreSQL 场景与性能测试之 22 - (OLTP) merge insert|upsert|insert on conflict|合并写入》

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

《PostgreSQL 如何实现upsert与新旧数据自动分离》

《[转载]postgresql 9.5版本之前实现upsert功能》

《upsert - PostgreSQL 9.4 pending patch : INSERT...ON DUPLICATE KEY IGNORE》

相关实践学习
使用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天前
|
关系型数据库 数据库
关系型数据库的数据完整性
关系型数据库通过一系列机制和技术手段来确保数据的完整性,从而为用户提供准确、一致和可靠的数据服务。这些措施对于保障数据的质量、满足用户需求以及维护系统的稳定运行具有重要意义。
23 5
|
15天前
|
关系型数据库 数据库 数据安全/隐私保护
关系型数据库的数据完整性保障
【5月更文挑战第9天】关系型数据库的数据完整性保障
20 1
|
3天前
|
关系型数据库 数据库 数据库管理
关系型数据库数据唯一性保证
【5月更文挑战第17天】
9 1
|
6天前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用合集之如何使用PostgreSQL2.4.1从指定时间戳同步数据
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
7天前
|
SQL 关系型数据库 数据库
实时计算 Flink版产品使用合集之同步PostgreSQL数据时,WAL 日志无限增长,是什么导致的
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
10天前
|
存储 监控 前端开发
关系型数据库数据输入验证
【5月更文挑战第12天】
43 5
|
10天前
|
存储 关系型数据库 数据库
关系型数据库的数据完整性约束
【5月更文挑战第12天】关系型数据库的数据完整性约束
23 2
|
13天前
|
存储 SQL Oracle
关系型数据库文件方式存储DATA FILE(数据文件)
【5月更文挑战第11天】关系型数据库文件方式存储DATA FILE(数据文件)
27 3
|
14天前
|
监控 安全 关系型数据库
关系型数据库数据完整性保障
【5月更文挑战第10天】关系型数据库数据完整性保障
29 4

相关产品

  • 云原生数据库 PolarDB