PostgreSQL merge insert(upsert/insert into on conflict) 如何区分数据是INSERT还是UPDATE-阿里云开发者社区

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

2019-04-27 2066

版权

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

本文涉及的产品

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里面会填充更新事务号。

2000元阿里云代金券免费领取，2核4G云服务器仅664元/3年，新老用户都有优惠，立即抢购>>>

阿里云采购季（云主机223元/3年）活动入口：请点击进入>>>，

阿里云学生服务器（9.5元/月）购买入口：请点击进入>>>，

背景

使用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)