開發(fā)中遇到這種情況，之前用的Upsert中需要知道當(dāng)前這條記錄是Insert還是Upsert，然后后臺根據(jù)SQL執(zhí)行的返回結(jié)果去做進一步的邏輯判斷，比如Insert的時候需要執(zhí)行其他操作。

當(dāng)然如果通過先Select的方式查一下在調(diào)用Upsert語句就可以實現(xiàn)的，但是這樣明顯會執(zhí)行兩次【程序】<-->【DB】之間的調(diào)用，就必然會消耗一部分的通信成本（往往可以忽略不計但是我就是想較真一下？？？強詞奪理中）

所以，通過什么方式來實現(xiàn)這個功能呢？有同學(xué)說寫過程?。ㄟ@里就不多說了，寫過程是可以的，但是實現(xiàn)這么簡單的功能專門寫個過程，而且也不利于后期遷移等問題，就不多說了）今天介紹一個通過With查詢語句和Returning來實現(xiàn)的判斷方法

首先我們建立一張表test，分別是主鍵id，列a和列b，然后通過語句插入一條數(shù)據(jù)：
INSERT INTO test VALUES (1,2,3);

再次執(zhí)行這條語句必然會說主鍵重復(fù)無法插入咯。所以通過Upsert方法我們來改造一下：
INSERT INTO test VALUES (1,2,3) ON CONFLICT (id) DO UPDATE SET a=3,b=4;

這樣，就可以順利插入或者更新當(dāng)前的這一條數(shù)據(jù)啦。那么如何知道數(shù)據(jù)庫是執(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 );

至此，如果上述語句中返回的是NULL，說明With沒有查詢到數(shù)據(jù)，也就是說執(zhí)行的是INSERT操作，如果返回的是1，則說明執(zhí)行了UPDATE操作。

新的轉(zhuǎn)機

剛開始想著這個問題應(yīng)該很簡單，所以就沒去百度，直到百度了一下才發(fā)現(xiàn)了新大陸
關(guān)于這個問題老早就有很多前輩們提出了各種方法（沒有用上面這種的，相比而言上面這種實在是太麻煩了，/(ㄒoㄒ)/~~）

那么是如何解決的呢？這里用到了一個PGSQL中的隱藏字段：xmax，那么什么是xmax呢？PGSQL中還有哪些系統(tǒng)的字段？詳見Tony老斯的這篇文章：【PostgreSQL中的系統(tǒng)字段：tableoid,xmin,xmax,cmin,cmax,ctid】，這里就不詳細(xì)描述了，大體轉(zhuǎn)發(fā)一下中心思想：

tableid

tableoid 字段代表了數(shù)據(jù)所在表的對象 id（OID），也就是數(shù)據(jù)字典表 pg_class 中與該表信息相關(guān)的數(shù)據(jù)行。tableoid 的另一個用途就是在涉及分區(qū)表查詢或者 UNION 操作時標(biāo)識數(shù)據(jù)行所在的具體表。

ctid

ctid 字段代表了數(shù)據(jù)行在表中的物理位置，也就是行標(biāo)識（tuple identifier），由一對數(shù)值組成（塊編號和行索引）。ctid 類似于 Oracle 中的偽列 ROWID。
ctid 可以用于快速查找表中的數(shù)據(jù)行，也可以用于修復(fù)數(shù)據(jù)損壞。另外，它也可以用于查找并刪除表中的重復(fù)數(shù)據(jù)。
需要注意的是，ctid 的值有可能會改變（例如 VACUUM FULL）；因此，ctid 不適合作為一個長期的行標(biāo)識，應(yīng)該使用主鍵作為行的邏輯標(biāo)識。

xmin

xmin 代表了該行版本（row version ）的插入事務(wù) ID（XID）。行版本是數(shù)據(jù)行的具體狀態(tài)，每次更新操作都會為相同的邏輯行創(chuàng)建一個新的行版本（多版本并發(fā)控制，MVCC）。事務(wù) ID 是一個 32 bit 數(shù)字。例如：

SELECT xmin,id FROM test;

xmin 字段可以用于查看數(shù)據(jù)行的插入時間：

SELECT id,to_char(pg_xact_commit_timestamp(xmin),'YYYY/MM/DD HH24:MI:SS') AS insert_time FROM test;

當(dāng)然使用該特性的時候需要開啟數(shù)據(jù)庫的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 字段代表了刪除該行的事務(wù) ID，對于未刪除的行版本顯示為 0。非零的 xmax 通常意味著刪除事務(wù)還沒有提交，或者刪除操作被回滾。
PostgreSQL 中的 UPDATE 相當(dāng)于 DELETE 加 INSERT。，所以在一個行執(zhí)行了Update操作后，該行的xmax就不會為0。
xmax 還有可能表示當(dāng)前正在占用行鎖的事務(wù) ID，利用 PostgreSQL 擴展插件 pageinspect 可以獲取詳細(xì)信息。

cmin

cmin 代表了插入事務(wù)中的命令標(biāo)識符（從 0 開始）。命令標(biāo)識符是一個 32 bit 數(shù)字。

cmax

cmax 代表了刪除事務(wù)中的命令標(biāo)識符，或者 0。

oid

如果使用 PostgreSQL 11 或者更早版本，還有一個隱藏的系統(tǒng)字段：oid。它代表了數(shù)據(jù)行的對象 ID，只有當(dāng)創(chuàng)建表時使用了WITH OIDS選項或者配置參數(shù)default_with_oids設(shè)置為 true 時才會創(chuàng)建這個字段。
從 PostgreSQL 12 開始，不再支持WITH OIDS選項，oid 只用于系統(tǒng)內(nèi)部。

如何區(qū)分？

了解了上面的幾個系統(tǒng)字段，可以很輕松想到，通過插入后返回xmax字段的值是否不為0，可以實現(xiàn)判斷：如果是UPDATE，XMAX里面會填充更新事務(wù)號。
注意直接用UPDATE語句更新的話，XMAX會寫入0，因為是新版本，而老版本上XMAX會填入更新事務(wù)號。

簡單示例：

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í)行上述語句
id    type
1     INSERT

===============再次執(zhí)行上述語句
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í)行上述語句
id    type
1     UPDATE
2     INSERT
3     INSERT
4     INSERT
5     INSERT

===============再次執(zhí)行上述語句
id    type
1     UPDATE
2     UPDATE
3     UPDATE
4     UPDATE
5     UPDATE

上述方法，只能用在Upsert的時候，為什么呢？假設(shè)如果是直接執(zhí)行或先執(zhí)行Update，會怎樣呢？

UPDATE test SET
    a = 1,b = 2 
WHERE
    id < 3 
RETURNING 
    id, xmin, xmax;

===============無論執(zhí)行多少次，xmax都會是0
===============因為Update相當(dāng)于先DELETE后INSERT
===============所以代表DELETE事務(wù)號的xmax在執(zhí)行update后保持為0
id    xmin      xmax
1     666       0
2     666       0

小結(jié)

1. insert into on conflict do update，返回xmax不等于0，表示update，等于0表示insert。
2. 直接update，并提交，提交的記錄上xmax為0。
3. 直接update，并回滾，老版本上的XMAX不為0，表示更新該行的事務(wù)號。
4. 直接DELETE，并回滾，老版本上的XMAX不為0，表示刪除該行的事務(wù)號。

ctid表示行號
xmin表示INSERT該記錄的事務(wù)號
xmax表示刪除該記錄(update實際上是刪除老版本新增新版本，所以老版本上xmax有值)的事務(wù)號。

參考資料：

【PostgreSQL merge insert(upsert/insert into on conflict) 如何區(qū)分?jǐn)?shù)據(jù)是INSERT還是UPDATE】