PostgreSQL多表批量插入关联数据报42601语法错误如何解决

错误原因 #

抛出42601语法错误的核心原因是多结果集拼接语法不符合规范：INSERT INTO ... SELECT 结构仅支持接收单个结果集，直接用逗号分隔多个SELECT子句属于语法错误，需要使用UNION ALL将多个查询返回的行合并为单个结果集后再执行插入。

修正后的可运行SQL #

BEGIN;
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('你的菜谱名称') RETURNING recipe_id
), ingredient1 AS (
    INSERT INTO ingredient (name) VALUES ('食材1') RETURNING ingredient_id
), ingredient2 AS (
    INSERT INTO ingredient (name) VALUES ('食材2') RETURNING ingredient_id
), ingredient3 AS (
    INSERT INTO ingredient (name) VALUES ('食材3') RETURNING ingredient_id
)
INSERT INTO recipes_ingredients (recipe_id, ingredient_id) 
SELECT new_recipe.recipe_id, ingredient1.ingredient_id FROM new_recipe CROSS JOIN ingredient1
UNION ALL
SELECT new_recipe.recipe_id, ingredient2.ingredient_id  FROM new_recipe CROSS JOIN ingredient2
UNION ALL
SELECT new_recipe.recipe_id, ingredient3.ingredient_id  FROM new_recipe CROSS JOIN ingredient3;
COMMIT;

UNION ALL作用是合并多个查询返回的行，当前无结果去重需求，性能高于UNION，完全适配该场景。

优化写法（可选） #

如果需要关联的食材数量较多，可采用批量插入的写法减少冗余CTE声明，代码更易维护：

BEGIN;
-- 插入菜谱获取ID
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('你的菜谱名称') RETURNING recipe_id
),
-- 批量插入所有食材获取全部ID
new_ingredients AS (
    INSERT INTO ingredient (name) 
    VALUES ('食材1'), ('食材2'), ('食材3')
    RETURNING ingredient_id
)
-- 交叉连接菜谱ID和食材ID批量插入关联表
INSERT INTO recipes_ingredients (recipe_id, ingredient_id)
SELECT new_recipe.recipe_id, new_ingredients.ingredient_id
FROM new_recipe CROSS JOIN new_ingredients;
COMMIT;

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