PostgreSQL中Upsert如何区分执行的是Insert还是Update?

开发中遇到这种情况，之前用的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】