PostgreSQL INSERT INTO table multiple records taken from multiple selects

Question

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

The syntax for cross joins is: from table_one cross join table_two cross join table_three ... — user330315
– user330315, Commented Nov 28, 2021 at 22:52

Himanshu · Accepted Answer · 2021-11-28 22:51:43Z

2

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;

edited Nov 28, 2021 at 22:51

answered Nov 28, 2021 at 22:46

Himanshu

3,9802 gold badges16 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

LukStorms Over a year ago

have you tested this? My guess is that UNION would work. But with ; only the first CTE insert would trigger.

Himanshu Over a year ago

Yeah my bad Union would work no semicolon just realized you had used CTE if you want you can have seperate CTEs either way union would be better choice and can leave the last semicolon before commit

LukStorms Over a year ago

I wonder, would there be a difference between UNION and UNION ALL if CTE's are used like this. (first time I've seen it on SO) ... Never mind, it'll just make a difference in the output. Unique id's with union.

zupus Over a year ago

It works with the UNION. Thanks!!

Collectives™ on Stack Overflow

PostgreSQL INSERT INTO table multiple records taken from multiple selects

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related