PostgreSQL中包含insert into子句的COALESCE语句执行失败问题

• 第一个语法错误是因为INSERT属于数据修改语句，不能直接作为标量子查询的内容放在函数参数中，标量子查询仅支持SELECT类查询语句，所以直接在coalesce的参数中写INSERT会触发语法报错。
• 第二个错误是因为PostgreSQL有明确的语法限制：包含INSERT/UPDATE/DELETE这类数据修改语句的CTE必须放在整个查询的最顶层，不能嵌套在子查询、函数参数等内部位置。你将带INSERT的CTE放在了coalesce的参数中，属于嵌套使用，不符合语法要求，因此触发了对应的报错。
  另外需要注意：coalesce的惰性求值特性仅适用于纯查询类的参数，对数据修改语句不生效，因为PostgreSQL的查询规划器不会对数据修改操作做惰性执行的优化，即使语法允许也无法达到你预期的“查不到才插入”的效果。

推荐两个符合PostgreSQL语法规范的实现方案，都可以实现“存在则返回已有id，不存在则插入后返回新id”的需求：

方案1：使用官方推荐的INSERT ... ON CONFLICT语法（优先推荐） #

这是PostgreSQL专门为Upsert场景设计的语法，并发安全性高、逻辑简洁：

INSERT INTO test (first_name)
VALUES ('carlos')
ON CONFLICT (first_name) DO UPDATE SET first_name = EXCLUDED.first_name
RETURNING id;

这里的DO UPDATE是无实际数据修改的空操作，仅用于触发RETURNING返回已存在行的id。

方案2：使用顶层CTE实现先查后插的逻辑 #

如果你确实不想用ON CONFLICT语法，可以把所有逻辑放在最顶层CTE中实现：

WITH select_row AS (
    SELECT id FROM test WHERE first_name = 'carlos'
), insert_row AS (
    INSERT INTO test (first_name)
    SELECT 'carlos'
    WHERE NOT EXISTS (SELECT 1 FROM select_row)
    RETURNING id
)
SELECT id FROM select_row
UNION ALL
SELECT id FROM insert_row;

该方案逻辑和你预期的完全一致：如果select_row查到了数据，insert_row中的INSERT会因为NOT EXISTS不成立而不会执行，最终返回已有的id；如果没查到数据，就会执行插入返回新id。

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