Postgres 9.5 UPSERT：插入成功返回null冲突时返回值可行吗？

完全可以实现你的需求！不过你最初尝试的ON CONFLICT DO SELECT语法在PostgreSQL 9.5里并不支持——ON CONFLICT子句只能搭配DO NOTHING或DO UPDATE。不过我们可以通过CTE（公共表表达式）来绕开这个限制，达成插入成功返回null、冲突时返回非null值且不写入磁盘的目标。

解决方案SQL #

WITH insert_attempt AS (
    INSERT INTO "user" (timestamp, user_id, member_id)
    VALUES ($1, $2, $3)
    ON CONFLICT (user_id, member_id) DO NOTHING
    RETURNING NULL AS conflict_result
),
conflict_check AS (
    SELECT user_id AS conflict_result
    FROM "user"
    WHERE user_id = $2 AND member_id = $3
)
SELECT conflict_result
FROM insert_attempt
UNION ALL
SELECT conflict_result
FROM conflict_check
WHERE NOT EXISTS (SELECT 1 FROM insert_attempt);

逻辑解释 #

• insert_attempt 部分：尝试执行插入操作。如果插入成功（无冲突），会返回一行包含NULL的结果；如果遇到冲突，DO NOTHING会让这个CTE不返回任何行，同时不会对磁盘做任何写入操作（完全符合你“不写入磁盘”的要求）。
• conflict_check 部分：专门检查是否存在与输入参数匹配的冲突记录，如果存在，返回对应的user_id作为非null标识。
• 最终查询部分：通过UNION ALL和WHERE NOT EXISTS合并两个CTE的结果：
  • 插入成功时，insert_attempt有返回值，conflict_check会被过滤掉，最终返回NULL；
  • 冲突发生时，insert_attempt无返回值，conflict_check的结果会被保留，最终返回user_id。

注意事项 #

• 确保conflict_check中的查询条件和你定义的唯一约束完全一致（这里是user_id + member_id），避免因为条件不全返回错误结果。
• 这个写法在并发场景下也能正常工作：即使在插入尝试和冲突检查之间有其他操作修改数据，只要冲突存在，conflict_check就能正确查到已存在的记录。

内容的提问来源于stack exchange，提问作者Jimski