开发者社区> pg小助手> 正文

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

简介: postgresql merge insert
+关注继续查看

背景
使用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》

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

相关文章
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里面会填充更新事务号。
1770 0
PostgreSQL 大宽表,全列索引,高并发合并写入(insert into on conflict, upsert, merge insert) - 实时adhoc query
标签 PostgreSQL , 全列索引 , 大宽表 , 写测试 , insert on conflict , upsert , merge insert , adhoc query 背景 OLAP系统中,adhoc query非常场景(任意维度查询分析)。 adhoc query,通常来说,可以加GIN倒排,或者每一列都加一个索引来实现。 《PostgreSQL 设计优化case
6426 0
Merge(在一条语句中使用Insert,Update,Delete) 对两个表进行同步数据
SQL Server 2008提供了一个增强的SQL命令Merge,用法参看MSDN:http://msdn.microsoft.com/zh-cn/library/bb510625.aspx 功能:根据与源表联接的结果,对目标表执行插入、更新或删除操作。
820 0
MySQL学习笔记汇总(三)——子查询、limit、表(insert,update,delete)
子查询、limit、表(insert,update,delete)的相关学习。
156 0
mysql字符集,insert,update,delete,select
发现有错误:数据太长了。//查看数据库的所有编码:show variables like 'character%';-----+| character_set_client     | utf8    设置客户端的字符集     || character_set_connection | utf8    设置连接的字符集     || character_set_database   | ut
978 0
PostgreSQL 分区表如何支持多列唯一约束 - 枚举、hash哈希 分区, 多列唯一, insert into on conflict, update, upsert, merge insert
标签 PostgreSQL , 分区表 , native partition , 唯一 , 非分区键唯一 , 组合唯一 , insert into on conflict , upsert , merge insert 背景 PG 11开始支持HASH分区,10的分区如果要支持hash分区,可以通过枚举绕道实现。 《PostgreSQL 9.x, 10, 11 hash分区表 用法举例
2378 0
T-SQL笔记2:INSERT、UPDATE和DELETE
T-SQL笔记2:INSERT、UPDATE和DELETE 本章摘要 1:显示向一个IDENTITY列插入值 2:在表中插入拥有UNIQUEIDENTIFIER列的行 3:使用INSERT……SELECT语句插入多行 4:调用存储过程插入数据 5:根据FROM和WHERE字句更新行 6:更新大值数据类型的列 7:使用OPENROWSET和BULK插入或更新图片文件 8:DELETE 9:截断表 10:使用TOP分块修改数据   1:显示向一个IDENTITY列插入值      IDENTITY通常用作代理键(代理键是指由数据库生成的唯一的主键)。
962 0
+关注
文章
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载