How to manage the insert on a junction table

Question

I have two tables: ingredient and recipe

recipe table
-----------------------
|recipe_id   |name  |
-----------------------
|1           |Pasta |
|2           |Pizza |
|3           |Fish  |


ingredient table
-------------------------------
|ingredient_id   |name        |
-------------------------------
|1               |Spaghetti   |
|2               |Salmon      |
|3               |Tomato sauce|

Then I have a junction table for these two tables.

---------------------------
|id|recipe_id|ingredient_id|
---------------------------
|1 |1        |1            |
|2 |2        |3            |
|3 |3        |2            |

It's not clear for me how I should insert data in the junction table. I mean, do I have to insert the recipe_id and ingredient_id manually with a simple INSERT? Or do I have to use the relations with the other two tables in some way?

insert the recipe_id and ingredient_id manually with a simple INSERT — Sergey, Nov 16 '21 at 09:56

score 1 · Accepted Answer · answered Nov 16 '21 at 10:02

Inserting a full relationship into all three tables will generally require three separate insert statements. You may deal with this problem by performing all inserts within a single logical transaction. For example:

BEGIN;  -- start transaction

INSERT INTO recipe (recipe_id, name) VALUES (1, 'Pasta');
INSERT INTO ingredient (ingredient_id, name) VALUES (1, 'Spagehetti');
INSERT INTO junction_table (recipe_id, ingredient_id) (1, 1);

COMMIT; -- end transaction

In practice, if the recipe_id and ingredient_id columns be serial/auto increment, then you would omit them from the insert statement. If you needed to find the auto generated ID values for these tables after the insert, you could use the pg_get_serial_sequence() function, see here.

score 1 · Answer 2 · answered Nov 16 '21 at 10:11

The tables recipe and ingredient are independent tables and data can be inserted by simple queries:

insert into recipe (name) values ('Pasta'), ('Pizza '), ('Fish');

insert into ingredient (name) values ('Spaghetti'), ('Salmon '), ('Tomato sauce');

The junction table can be filled by select data for both tables like:

insert into recipe_ingredient (recipe_id, ingredient_id)
select recipe_id, ingredient_id
from recipe, ingredient
where recipe.name = 'Pasta' and ingredient.name in ('Spaghetti', 'Tomato sauce');

Try PostgreSQL fiddle

How to manage the insert on a junction table

2 Answers2