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
CodePudding user response:
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;