PostgreSQL INSERT INTO table multiple records taken from multiple selects

byMR

November 29, 2021

I have these 3 tables

recipe: recipe_id | name

ingredient: ingredient_id | name

recipes_ingredients: id | recipe_id | ingredient_id

The first id of every table is a SERIAL PRIMARY KEY and the two names are character varying(50). I’m trying to insertrecipe_id and ingredient_id in recipes_ingredients and if I do it with a single ingredient it works perfectly. The problem is that I don’t know how to insert multiple ingredient associated with a single recipe.

This is what I tried to insert 3 different ingredients associated with the same recipe:

BEGIN;
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('{}') RETURNING recipe_id
), ingredient1 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient2 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient3 AS (
    INSERT INTO ingredient (name) VALUES ('{}') 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,
SELECT new_recipe.recipe_id, ingredient2.ingredient_id  FROM new_recipe CROSS JOIN ingredient2,
SELECT new_recipe.recipe_id, ingredient3.ingredient_id  FROM new_recipe CROSS JOIN ingredient3
COMMIT;

It gives me this error:

ERROR:  syntax error at or near "SELECT"
LINE 13: SELECT new_recipe.recipe_id, ingredient2.ingredient_id  FROM...
         ^
SQL state: 42601
Character: 520

>Solution :

Try replacing , with a union/union all after each select

BEGIN;
WITH new_recipe AS (
    INSERT INTO recipe (name) VALUES ('{}') RETURNING recipe_id
), ingredient1 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient2 AS (
    INSERT INTO ingredient (name) VALUES ('{}') RETURNING ingredient_id
), ingredient3 AS (
    INSERT INTO ingredient (name) VALUES ('{}') 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
SELECT new_recipe.recipe_id, ingredient2.ingredient_id  FROM new_recipe CROSS JOIN ingredient2 Union
SELECT new_recipe.recipe_id, ingredient3.ingredient_id  FROM new_recipe CROSS JOIN ingredient3;
COMMIT;