INSERT返回ID无法插入关联表且WITH子句报语法错误如何解决

错误原因 #

• 语法规则问题：PostgreSQL 中定义多个 CTE（公共表表达式）不需要重复写 WITH 关键字，仅需在第一个 CTE 前声明一次 WITH，后续多个 CTE 之间用逗号分隔即可，你重复写了第二个 WITH 触发了语法报错。
• 逻辑实现问题：最终向关联表插入数据时，不能直接在 VALUES 子句中引用 CTE 的返回字段，需要通过 SELECT 从两个 CTE 中取出返回的ID再完成插入。

修正后的SQL语句 #

BEGIN;  -- start transaction
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('替换为实际菜谱名称') RETURNING recipe_id
),
new_ingredient AS (
    INSERT INTO ingredient (name) VALUES ('替换为实际食材名称') RETURNING ingredient_id
)
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)
SELECT new_recipe.recipe_id, new_ingredient.ingredient_id
FROM new_recipe, new_ingredient;
COMMIT; -- end transaction

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