PostgreSQL插入冲突时返回冲突行标识符的实现问题

嘿，我完全懂你这个困扰！之前我也遇到过一模一样的问题——用ON CONFLICT DO NOTHING加RETURNING的时候，冲突发生就啥都返回不了，确实头疼。

问题出在ON CONFLICT DO NOTHING的逻辑上：它只是跳过了插入操作，并不会去查询冲突行的信息，所以RETURNING自然没有数据可以返回。要实现“插入成功返回新ID，冲突时返回已有行ID”的需求，我们可以换用ON CONFLICT ... DO UPDATE来实现，哪怕我们不需要实际更新任何数据。

假设你的唯一约束是定义在domain和email这两个字段上的，你可以用下面的SQL语句：

INSERT INTO operations.my_table (domain, email)
VALUES ('my_domain', 'email@my_domain')
ON CONFLICT (domain, email) DO UPDATE 
SET domain = my_table.domain  -- 执行一个空更新，不会修改任何数据
RETURNING my_table_id;

为什么这个方法有效？ #

• 当插入操作没有冲突时，正常执行插入，RETURNING会返回新插入行的my_table_id；
• 当发生唯一约束冲突时，DO UPDATE分支会被触发，我们这里只是把domain字段设为它当前的值（相当于没做任何修改），但这时候RETURNING会返回冲突行的my_table_id；

如果你的唯一约束有自定义名称，也可以直接指定约束名，这样更清晰：

INSERT INTO operations.my_table (domain, email)
VALUES ('my_domain', 'email@my_domain')
ON CONFLICT ON CONSTRAINT my_table_domain_email_unique DO UPDATE 
SET domain = my_table.domain
RETURNING my_table_id;

这样不管是插入成功还是遇到冲突，你都能拿到对应的行标识符啦！

备注：内容来源于stack exchange，提问作者Jeroen Vermunt