PostgreSQL插入触发唯一约束冲突时如何返回已存在记录的ID

你原有语句无法返回冲突时的已有ID，是因为 ON CONFLICT DO NOTHING 触发冲突时没有任何行被实际写入，RETURNING 子句仅会返回真正执行插入操作的行数据，因此冲突场景下返回结果为空。

方案1：使用无实际变更的DO UPDATE触发返回（推荐绝大多数场景使用） #

利用ON CONFLICT DO UPDATE执行无实际数据修改的更新操作，即可触发RETURNING返回对应行ID，无论插入还是冲突都能拿到结果：

INSERT INTO my_table (my_column) 
VALUES ('my value') 
ON CONFLICT (my_column) DO UPDATE 
-- 此处赋值不会产生实际数据变更，仅用于触发返回逻辑
SET my_column = EXCLUDED.my_column
RETURNING id;

其中EXCLUDED是PostgreSQL内置关键字，指代本次插入操作原本要写入的行数据。

方案2：CTE组合插入+查询（适合需要避免无意义更新的场景） #

如果不希望执行无意义的UPDATE操作（比如要避免触发UPDATE相关触发器、最大化写入性能），可以用CTE先尝试插入，插入失败时再查询已有数据的ID：

WITH inserted AS (
    INSERT INTO my_table (my_column)
    VALUES ('my value')
    ON CONFLICT DO NOTHING
    RETURNING id
)
-- 插入成功则返回插入的ID，失败则返回查询到的已有ID
SELECT id FROM inserted
UNION ALL
SELECT id FROM my_table WHERE my_column = 'my value'
LIMIT 1;

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