開發(fā)中遇到這種情況县貌，之前用的Upsert中需要知道當(dāng)前這條記錄是Insert還是Upsert铆遭，然后后臺(tái)根據(jù)SQL執(zhí)行的返回結(jié)果去做進(jìn)一步的邏輯判斷，比如Insert的時(shí)候需要執(zhí)行其他操作膘螟。
當(dāng)然如果通過(guò)先Select的方式查一下在調(diào)用Upsert語(yǔ)句就可以實(shí)現(xiàn)的，但是這樣明顯會(huì)執(zhí)行兩次【程序】<-->【DB】之間的調(diào)用碾局，就必然會(huì)消耗一部分的通信成本（往往可以忽略不計(jì)但是我就是想較真一下荆残？？净当？強(qiáng)詞奪理中）
所以内斯，通過(guò)什么方式來(lái)實(shí)現(xiàn)這個(gè)功能呢？有同學(xué)說(shuō)寫過(guò)程跋裉洹（這里就不多說(shuō)了俘闯，寫過(guò)程是可以的，但是實(shí)現(xiàn)這么簡(jiǎn)單的功能專門寫個(gè)過(guò)程忽冻，而且也不利于后期遷移等問(wèn)題真朗，就不多說(shuō)了）今天介紹一個(gè)通過(guò)With查詢語(yǔ)句和Returning來(lái)實(shí)現(xiàn)的判斷方法
首先我們建立一張表test，分別是主鍵id僧诚，列a和列b遮婶，然后通過(guò)語(yǔ)句插入一條數(shù)據(jù)：
INSERT INTO test VALUES (1,2,3);
再次執(zhí)行這條語(yǔ)句必然會(huì)說(shuō)主鍵重復(fù)無(wú)法插入咯。所以通過(guò)Upsert方法我們來(lái)改造一下：
INSERT INTO test VALUES (1,2,3) ON CONFLICT (id) DO UPDATE SET a=3,b=4;
這樣湖笨，就可以順利插入或者更新當(dāng)前的這一條數(shù)據(jù)啦旗扑。那么如何知道數(shù)據(jù)庫(kù)是執(zhí)行了插入還是更新呢？大體思路如下：
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 );
至此慈省，如果上述語(yǔ)句中返回的是NULL臀防，說(shuō)明With沒(méi)有查詢到數(shù)據(jù)，也就是說(shuō)執(zhí)行的是INSERT操作辫呻，如果返回的是1，則說(shuō)明執(zhí)行了UPDATE操作琼锋。
新的轉(zhuǎn)機(jī)
剛開始想著這個(gè)問(wèn)題應(yīng)該很簡(jiǎn)單放闺，所以就沒(méi)去百度，直到百度了一下才發(fā)現(xiàn)了新大陸
關(guān)于這個(gè)問(wèn)題老早就有很多前輩們提出了各種方法（沒(méi)有用上面這種的缕坎，相比而言上面這種實(shí)在是太麻煩了怖侦，/(ㄒoㄒ)/~~）
那么是如何解決的呢？這里用到了一個(gè)PGSQL中的隱藏字段：xmax谜叹，那么什么是xmax呢匾寝？PGSQL中還有哪些系統(tǒng)的字段？詳見(jiàn)Tony老斯的這篇文章：【PostgreSQL中的系統(tǒng)字段：tableoid,xmin,xmax,cmin,cmax,ctid】荷腊，這里就不詳細(xì)描述了艳悔，大體轉(zhuǎn)發(fā)一下中心思想：
tableid
tableoid 字段代表了數(shù)據(jù)所在表的對(duì)象 id（OID），也就是數(shù)據(jù)字典表 pg_class 中與該表信息相關(guān)的數(shù)據(jù)行女仰。tableoid 的另一個(gè)用途就是在涉及分區(qū)表查詢或者 UNION 操作時(shí)標(biāo)識(shí)數(shù)據(jù)行所在的具體表猜年。
ctid
ctid 字段代表了數(shù)據(jù)行在表中的物理位置抡锈，也就是行標(biāo)識(shí)（tuple identifier），由一對(duì)數(shù)值組成（塊編號(hào)和行索引）乔外。ctid 類似于 Oracle 中的偽列 ROWID床三。
ctid 可以用于快速查找表中的數(shù)據(jù)行，也可以用于修復(fù)數(shù)據(jù)損壞杨幼。另外撇簿，它也可以用于查找并刪除表中的重復(fù)數(shù)據(jù)。
需要注意的是差购，ctid 的值有可能會(huì)改變（例如 VACUUM FULL）四瘫；因此，ctid 不適合作為一個(gè)長(zhǎng)期的行標(biāo)識(shí)歹撒，應(yīng)該使用主鍵作為行的邏輯標(biāo)識(shí)莲组。
xmin
xmin 代表了該行版本（row version ）的插入事務(wù) ID（XID）。行版本是數(shù)據(jù)行的具體狀態(tài)暖夭，每次更新操作都會(huì)為相同的邏輯行創(chuàng)建一個(gè)新的行版本（多版本并發(fā)控制锹杈，MVCC）。事務(wù) ID 是一個(gè) 32 bit 數(shù)字迈着。例如：
SELECT xmin,id FROM test;
xmin 字段可以用于查看數(shù)據(jù)行的插入時(shí)間：
SELECT id,to_char(pg_xact_commit_timestamp(xmin),'YYYY/MM/DD HH24:MI:SS') AS insert_time FROM test;
當(dāng)然使用該特性的時(shí)候需要開啟數(shù)據(jù)庫(kù)的track_commit_timestamp配置竭望，否則會(huì)報(bào)錯(cuò)：
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 字段代表了刪除該行的事務(wù) ID咬清，對(duì)于未刪除的行版本顯示為 0。非零的 xmax 通常意味著刪除事務(wù)還沒(méi)有提交奴潘，或者刪除操作被回滾旧烧。
PostgreSQL 中的 UPDATE 相當(dāng)于 DELETE 加 INSERT。画髓，所以在一個(gè)行執(zhí)行了Update操作后掘剪，該行的xmax就不會(huì)為0。
xmax 還有可能表示當(dāng)前正在占用行鎖的事務(wù) ID奈虾，利用 PostgreSQL 擴(kuò)展插件 pageinspect 可以獲取詳細(xì)信息夺谁。
cmin
cmin 代表了插入事務(wù)中的命令標(biāo)識(shí)符（從 0 開始）。命令標(biāo)識(shí)符是一個(gè) 32 bit 數(shù)字肉微。
cmax
cmax 代表了刪除事務(wù)中的命令標(biāo)識(shí)符匾鸥，或者 0。
oid
如果使用 PostgreSQL 11 或者更早版本碉纳，還有一個(gè)隱藏的系統(tǒng)字段：oid勿负。它代表了數(shù)據(jù)行的對(duì)象 ID，只有當(dāng)創(chuàng)建表時(shí)使用了WITH OIDS選項(xiàng)或者配置參數(shù)default_with_oids設(shè)置為 true 時(shí)才會(huì)創(chuàng)建這個(gè)字段劳曹。
從 PostgreSQL 12 開始笆环，不再支持WITH OIDS選項(xiàng)攒至，oid 只用于系統(tǒng)內(nèi)部。
如何區(qū)分躁劣？
了解了上面的幾個(gè)系統(tǒng)字段迫吐，可以很輕松想到，通過(guò)插入后返回xmax字段的值是否不為0账忘，可以實(shí)現(xiàn)判斷：如果是UPDATE志膀，XMAX里面會(huì)填充更新事務(wù)號(hào)。
注意直接用UPDATE語(yǔ)句更新的話鳖擒，XMAX會(huì)寫入0溉浙，因?yàn)槭切掳姹荆习姹旧蟈MAX會(huì)填入更新事務(wù)號(hào)蒋荚。
簡(jiǎn)單示例：
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;
===============首次執(zhí)行上述語(yǔ)句
id type
1 INSERT
===============再次執(zhí)行上述語(yǔ)句
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;
===============首次執(zhí)行上述語(yǔ)句
id type
1 UPDATE
2 INSERT
3 INSERT
4 INSERT
5 INSERT
===============再次執(zhí)行上述語(yǔ)句
id type
1 UPDATE
2 UPDATE
3 UPDATE
4 UPDATE
5 UPDATE
上述方法戳稽，只能用在Upsert的時(shí)候，為什么呢期升？假設(shè)如果是直接執(zhí)行或先執(zhí)行Update惊奇，會(huì)怎樣呢？
UPDATE test SET
a = 1,b = 2
WHERE
id < 3
RETURNING
id, xmin, xmax;
===============無(wú)論執(zhí)行多少次播赁，xmax都會(huì)是0
===============因?yàn)閁pdate相當(dāng)于先DELETE后INSERT
===============所以代表DELETE事務(wù)號(hào)的xmax在執(zhí)行update后保持為0
id xmin xmax
1 666 0
2 666 0
小結(jié)
-
insert into on conflict do update颂郎，返回xmax不等于0，表示update容为，等于0表示insert乓序。 - 直接
update，并提交坎背，提交的記錄上xmax為0替劈。 - 直接
update，并回滾得滤，老版本上的XMAX不為0陨献，表示更新該行的事務(wù)號(hào)。 - 直接
DELETE耿戚，并回滾湿故，老版本上的XMAX不為0阿趁，表示刪除該行的事務(wù)號(hào)膜蛔。
ctid表示行號(hào)
xmin表示INSERT該記錄的事務(wù)號(hào)
xmax表示刪除該記錄(update實(shí)際上是刪除老版本新增新版本，所以老版本上xmax有值)的事務(wù)號(hào)脖阵。
參考資料：
【PostgreSQL merge insert(upsert/insert into on conflict) 如何區(qū)分?jǐn)?shù)據(jù)是INSERT還是UPDATE】
