如何确定 upsert 是否是 PostgreSQL 9.5+ UPSERT 的更新？

可写 CTE 在 9.5 之前被认为是 UPSERT 的解决方案，如中所述在 PostgreSQL 中重复更新时插入？ https://stackoverflow.com/questions/1109061/insert-on-duplicate-update-in-postgresql/8702291#8702291

可以使用以下可写 CTE 习惯用法来执行 UPSERT，无论它最终是更新还是插入：

WITH
    update_cte AS (
        UPDATE t SET v = $1 WHERE id = $2 RETURNING 'updated'::text status
    ),
    insert_cte AS (
        INSERT INTO t(id, v) SELECT $2, $1 WHERE NOT EXISTS
            (SELECT 1 FROM update_cte) RETURNING 'inserted'::text status
    )
 (SELECT status FROM update_cte) UNION (SELECT status FROM insert_cte)

此查询将返回“已更新”或“已插入”，或者可能（很少）因违反约束而失败，如中所述https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates

使用 PostgreSQL 9.5+ 新的“UPSERT”语法可以实现类似的效果，受益于其优化并避免可能的约束违规吗？

我相信xmax::text::int > 0将是最简单的技巧：

so=# DROP TABLE IF EXISTS tab;
NOTICE:  table "tab" does not exist, skipping
DROP TABLE
so=# CREATE TABLE tab(id INT PRIMARY KEY, col text);
CREATE TABLE
so=# INSERT INTO tab(id, col) VALUES (1,'a');
INSERT 0 2
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |   case   | ctid
----+------+----------+-------
  3 | c    | inserted | (0,3)
  4 | d    | inserted | (0,4)
  1 | aaaa | updated  | (0,5)
(3 rows)

INSERT 0 3
so=# INSERT INTO tab(id, col)
VALUES (3, 'c'), (4, 'd'), (1,'aaaa')
ON CONFLICT (id) DO UPDATE SET col = EXCLUDED.col
returning *,case when xmax::text::int > 0 then 'updated' else 'inserted' end,ctid;
 id | col  |  case   | ctid
----+------+---------+-------
  3 | c    | updated | (0,6)
  4 | d    | updated | (0,7)
  1 | aaaa | updated | (0,8)
(3 rows)

INSERT 0 3