开发中遇到这种情况，之前用的Upsert中需要知道当前这条记录是Insert还是Upsert，然后后台根据SQL执行的返回结果去做进一步的逻辑判断，比如Insert的时候需要执行其他操作。
当然如果通过先Select的方式查一下在调用Upsert语句就可以实现的，但是这样明显会执行两次【程序】<-->【DB】之间的调用，就必然会消耗一部分的通信成本（往往可以忽略不计但是我就是想较真一下？？？强词夺理中）
所以，通过什么方式来实现这个功能呢？有同学说写过程啊（这里就不多说了，写过程是可以的，但是实现这么简单的功能专门写个过程，而且也不利于后期迁移等问题，就不多说了）今天介绍一个通过With查询语句和Returning来实现的判断方法
首先我们建立一张表test，分别是主键id，列a和列b，然后通过语句插入一条数据：
INSERT INTO test VALUES (1,2,3);
再次执行这条语句必然会说主键重复无法插入咯。所以通过Upsert方法我们来改造一下：
INSERT INTO test VALUES (1,2,3) ON CONFLICT (id) DO UPDATE SET a=3,b=4;
这样，就可以顺利插入或者更新当前的这一条数据啦。那么如何知道数据库是执行了插入还是更新呢？大体思路如下：
WITH tt AS ( SELECT 1 AS abc FROM test WHERE id = 1 ) INSERT INTO test
VALUES
( 1, 2, 3 ) ON CONFLICT ( id ) DO
UPDATE
SET a = 5, b = 6
RETURNING ( SELECT abc FROM tt );
至此，如果上述语句中返回的是NULL，说明With没有查询到数据，也就是说执行的是INSERT操作，如果返回的是1，则说明执行了UPDATE操作。
新的转机
刚开始想着这个问题应该很简单，所以就没去百度，直到百度了一下才发现了新大陆
关于这个问题老早就有很多前辈们提出了各种方法（没有用上面这种的，相比而言上面这种实在是太麻烦了，/(ㄒoㄒ)/~~）
那么是如何解决的呢？这里用到了一个PGSQL中的隐藏字段：xmax，那么什么是xmax呢？PGSQL中还有哪些系统的字段？详见Tony老斯的这篇文章：【PostgreSQL中的系统字段：tableoid,xmin,xmax,cmin,cmax,ctid】，这里就不详细描述了，大体转发一下中心思想：
tableid
tableoid 字段代表了数据所在表的对象 id（OID），也就是数据字典表 pg_class 中与该表信息相关的数据行。tableoid 的另一个用途就是在涉及分区表查询或者 UNION 操作时标识数据行所在的具体表。
ctid
ctid 字段代表了数据行在表中的物理位置，也就是行标识（tuple identifier），由一对数值组成（块编号和行索引）。ctid 类似于 Oracle 中的伪列 ROWID。
ctid 可以用于快速查找表中的数据行，也可以用于修复数据损坏。另外，它也可以用于查找并删除表中的重复数据。
需要注意的是，ctid 的值有可能会改变（例如 VACUUM FULL）；因此，ctid 不适合作为一个长期的行标识，应该使用主键作为行的逻辑标识。
xmin
xmin 代表了该行版本（row version ）的插入事务 ID（XID）。行版本是数据行的具体状态，每次更新操作都会为相同的逻辑行创建一个新的行版本（多版本并发控制，MVCC）。事务 ID 是一个 32 bit 数字。例如：
SELECT xmin,id FROM test;
xmin 字段可以用于查看数据行的插入时间：
SELECT id,to_char(pg_xact_commit_timestamp(xmin),'YYYY/MM/DD HH24:MI:SS') AS insert_time FROM test;
当然使用该特性的时候需要开启数据库的track_commit_timestamp配置，否则会报错：
ERROR: could not get commit timestamp data
HINT: Make sure the configuration parameter "track_commit_timestamp" is set.
开启的方法如下：
1. 编辑postgresql.conf，添加配置先如下：
2. track_commit_timestamp = on
3. 接着重启PostgreSQL。
xmax
xmax 字段代表了删除该行的事务 ID，对于未删除的行版本显示为 0。非零的 xmax 通常意味着删除事务还没有提交，或者删除操作被回滚。
PostgreSQL 中的 UPDATE 相当于 DELETE 加 INSERT。，所以在一个行执行了Update操作后，该行的xmax就不会为0。
xmax 还有可能表示当前正在占用行锁的事务 ID，利用 PostgreSQL 扩展插件 pageinspect 可以获取详细信息。
cmin
cmin 代表了插入事务中的命令标识符（从 0 开始）。命令标识符是一个 32 bit 数字。
cmax
cmax 代表了删除事务中的命令标识符，或者 0。
oid
如果使用 PostgreSQL 11 或者更早版本，还有一个隐藏的系统字段：oid。它代表了数据行的对象 ID，只有当创建表时使用了WITH OIDS选项或者配置参数default_with_oids设置为 true 时才会创建这个字段。
从 PostgreSQL 12 开始，不再支持WITH OIDS选项，oid 只用于系统内部。
如何区分？
了解了上面的几个系统字段，可以很轻松想到，通过插入后返回xmax字段的值是否不为0，可以实现判断：如果是UPDATE，XMAX里面会填充更新事务号。
注意直接用UPDATE语句更新的话，XMAX会写入0，因为是新版本，而老版本上XMAX会填入更新事务号。
简单示例：
INSERT INTO test VALUES
( 1, 2, 3 )
ON CONFLICT ( id ) DO UPDATE SET
a = 5,b = 6
RETURING
id, ( CASE WHEN xmax <> 0 THEN 'UPDATE' ELSE 'INSERT' END ) AS type;
===============首次执行上述语句
id type
1 INSERT
===============再次执行上述语句
id type
1 UPDATE
批量Upsert示例：
INSERT INTO test VALUES
( 1, 2, 3 ),
( 2, 3, 4 ),
( 3, 4, 5 ),
( 4, 5, 6 ),
( 5, 6, 7 )
ON CONFLICT ( id ) DO UPDATE SET
a = 'a', b = 'b'
RETURNING
id, ( CASE WHEN xmax <> 0 THEN 'UPDATE' ELSE 'INSERT' END ) AS type;
===============首次执行上述语句
id type
1 UPDATE
2 INSERT
3 INSERT
4 INSERT
5 INSERT
===============再次执行上述语句
id type
1 UPDATE
2 UPDATE
3 UPDATE
4 UPDATE
5 UPDATE
上述方法，只能用在Upsert的时候，为什么呢？假设如果是直接执行或先执行Update，会怎样呢？
UPDATE test SET
a = 1,b = 2
WHERE
id < 3
RETURNING
id, xmin, xmax;
===============无论执行多少次，xmax都会是0
===============因为Update相当于先DELETE后INSERT
===============所以代表DELETE事务号的xmax在执行update后保持为0
id xmin xmax
1 666 0
2 666 0
小结
-
insert into on conflict do update，返回xmax不等于0，表示update，等于0表示insert。 - 直接
update，并提交，提交的记录上xmax为0。 - 直接
update，并回滚，老版本上的XMAX不为0，表示更新该行的事务号。 - 直接
DELETE，并回滚，老版本上的XMAX不为0，表示删除该行的事务号。
ctid表示行号
xmin表示INSERT该记录的事务号
xmax表示删除该记录(update实际上是删除老版本新增新版本，所以老版本上xmax有值)的事务号。
参考资料：
【PostgreSQL merge insert(upsert/insert into on conflict) 如何区分数据是INSERT还是UPDATE】
网友评论