Postgres 9.5冲突选择 [英] Postgres 9.5 ON CONFLICT DO SELECT

问题描述

在Postgres 9.5中执行UPSERT时，是否可以在插入成功时返回null并在冲突时返回某些内容？

While doing UPSERT in Postgres 9.5, is it possible to return null on INSERT success and return something ON CONFLICT?

我想要这样的东西：

insert into "user" (timestamp, user_id, member_id) 
values ($1, $2, $3)
ON CONFLICT (user_id, member_id) 
DO select id from "user" where user_id = $2 returning user_id

选择user_id的唯一目的是在冲突时返回某些内容（除了null以外的任何内容），而无需写入磁盘。我知道可以使用ON CONFLICT DO UPDATE来完成，但这将涉及写入磁盘。

The only purpose of selecting user_id is to return something (anything other than null) ON CONFLICT, and without writing to disk. I know that this can be done with ON CONFLICT DO UPDATE but it would involve writing to disk.

推荐答案

您可以使用CTE：

WITH cte AS (
   INSERT INTO "user"(timestamp, user_id, member_id)
   values ($1, $2, $3)
   ON CONFLICT (user_id, member_id) DO NOTHING
   RETURNING user_id
)
SELECT NULL AS result
WHERE EXISTS (SELECT 1 FROM cte)          -- success
UNION ALL
SELECT id 
FROM "user" 
WHERE user_id = $2 
  AND NOT EXISTS (SELECT 1 FROM cte);     -- conflict

DBFiddle演示

DBFiddle Demo

这篇关于Postgres 9.5冲突选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！