开发者社区> 德哥> 正文

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

简介: 标签 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》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
将表里的数据批量生成INSERT语句的存储过程 增强版
原文:将表里的数据批量生成INSERT语句的存储过程 增强版 将表里的数据批量生成INSERT语句的存储过程 增强版 有时候,我们需要将某个表里的数据全部或者根据查询条件导出来,迁移到另一个相同结构的库中 目前SQL Server里面是没有相关的工具根据查询条件来生成INSERT语句的,只有借助...
791 0
Python之pandas:数据类型变换之object、category、bool、int32、int64、float64以及数据类型标准化之详细攻略
Python之pandas:数据类型变换之object、category、bool、int32、int64、float64以及数据类型标准化之详细攻略
331 0
Mongo:update更新多条数据
Mongo:update更新多条数据
44 0
Oracle INTERVAL DAY TO SECOND数据类型
INTERVAL DAY TO SECOND数据类型 Oracle语法: INTERVAL '{ integer | integer time_expr | time_expr }' { { ...
791 0
Android开发12——Andorid中操作数据库的insert的两种方法以及nullColumnHack
一、发现问题 先看两种方法插入数据 public void save(Person p) { SQLiteDatabase db = dbHelper.getWritableDatabase(); db.execSQL("insert into person(name,phone) values (?,?)", new Object[] { p.getName(), p.ge
909 0
Spring JDBCTemplate使用JNDI数据源
xml配置: 1 3 4 5 6 7 在weblogic/jboss中配置好JNDI数据源后,上述节点改为: 1 2 3 j...
938 0
7.数据本地化CCString,CCArray,CCDictionary,tinyxml2,写入UserDefault.xml文件,操作xml,解析xml
 数据本地化 A CCUserDefault 系统会在默认路径cocos2d-x-2.2.3\projects\Hello\proj.win32\Debug.win32下生成一个名为UserDefault.xml.所有的key皆为char *型,value类型为bool intfloat double std::string. 读操作
1283 0
Contoso 大学 - 6 – 更新关联数据
原文 Contoso 大学 - 6 – 更新关联数据 By Tom Dykstra, Tom Dykstra is a Senior Programming Writer on Microsoft's Web Platform & Tools Content Team.
786 0
+关注
德哥
公益是一辈子的事, I'm digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
JS零基础入门教程(上册)
立即下载
性能优化方法论
立即下载
手把手学习日志服务SLS,云启实验室实战指南
立即下载