如何确定upsert是否是PostgreSQL 9.5+ UPSERT的更新? [英] How to find out if an upsert was an update with PostgreSQL 9.5+ UPSERT?
问题描述
是否可以使用信息将其最终更新为INSERT或执行INSERT来执行UPSERT以下可写CTE习惯用语:
It is possible to perform an UPSERT with the information whether it ended up as an UPDATE or an INSERT with the following Writable CTEs idiom:
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)
此查询将返回 updated或 inserted,或者可能(很少)由于违反约束而失败,如 https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates
This query will return either "updated" or "inserted", or may (rarely) fail with a constraint violation in as described in https://dba.stackexchange.com/questions/78510/why-is-cte-open-to-lost-updates
可以使用PostgreSQL 9.5+新的 UPSERT语法实现类似的功能,并受益于其优化并避免了可能的约束冲突吗?
Can something similar be achieved using PostgreSQL 9.5+ new "UPSERT" syntax, benefiting from its optimization and avoiding the possible constraint violation?
推荐答案
我相信 xmax :: text :: int> 0
是最简单的把戏:
I believe xmax::text::int > 0
would be the easiest trick:
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'), (2, 'b');
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
这篇关于如何确定upsert是否是PostgreSQL 9.5+ UPSERT的更新?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!