WITH upsert AS (UPDATE tbl SET a = 2 WHERE a = 1 RETURNING tbl.*)
INSERT INTO tbl (a)
SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
RETURNING *这个"upsert“语句工作,但我想检索更新或插入的值。当upsert执行INSERT (行尚未在db中)时,插入的值将从查询中正确返回。
但是,当执行UPDATE时,不返回值(但是行被更新)。我尝试过添加RETURNING upsert.*,但是它屏蔽了错误missing FROM-clause entry for table \"upsert\"。
发布于 2015-03-23 14:18:07
插入时需要使用第二个CTE:
WITH upsert AS (
UPDATE tbl SET a = 2
WHERE a = 1
RETURNING tbl.*
), inserted AS (
INSERT INTO tbl (a)
SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
RETURNING *
)
select *
from upsert
union all
select *
from insertedhttps://dba.stackexchange.com/questions/95979
复制相似问题