可写CTE被认为是9.5000之前的UPSERT解决方案,如
Insert,on duplicate update in PostgreSQL?所述
可以使用以下信息执行UPSERT：它是否作为UPDATE或INSERT结束,具有以下可写CTE成语：
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所述的约束违规操作失败
从
@lad2025’s answer年起,结果可以通过将
settings和
customized options与
related functions在WHERE子句中滥用来获得所需的副作用.
CREATE TABLE t(id INT PRIMARY KEY,v TEXT);
INSERT INTO t (id,v)
SELECT $1,$2
WHERE 'inserted' = set_config('upsert.action','inserted',true)
ON CONFLICT (id) DO UPDATE
SET v = EXCLUDED.v
WHERE 'updated' = set_config('upsert.action','updated',true)
RETURNING current_setting('upsert.action') AS "upsert.action";
set_config的第三个参数是is_local：true表示在事务结束时设置将消失.更确切地说,current_setting(‘upsert.action’)将返回NULL(而不是抛出一个错误)直到会话结束.
