INSERT auto increment primary keys into another table inside transaction

Question

I have 3 tables recipe, ingredient, and the junction table for the previous two recipes_ingredients

recipe: recipe_id | name | process

ingredient: ingredient_id | name| type

recipes_ingredients: id | recipe_id | ingredient_id

When I INSERT a record in recipe I want to insert also records for the ingredients of this recipe in ingredient. To relate the two tables I have to insert a record in recipes_ingredients for every ingredient. I thought to use a transaction an do something like this:

BEGIN;  -- start transaction

INSERT INTO recipe (recipe_id, name, process) VALUES ();
INSERT INTO ingredient (ingredient_id, name, type) VALUES ();
INSERT INTO recipes_ingredients (recipe_id, ingredient_id) VALUES ();

COMMIT; -- end transaction

The problem is that every primary key is SERIAL data type, so is automatically inserted with auto increment. So how can I insert recipe_id and ingredient_id in recipes_ingredients if I don't know them?

You will have to use the scope_Identity function equivalent in postgresql. read this https://learn.microsoft.com/en-us/sql/t-sql/functions/scope-identity-transact-sql?view=sql-server-ver15#:~:text=SCOPE_IDENTITY%20%28%29%20returns%20the%20value%20from%20the%20insert,inserted%20identity%20value.%20Here%20is%20the%20result%20set. — Sachin Vishwakarma, Nov 26 '21 at 16:58

INSERT auto increment primary keys into another table inside transaction

0 Answers0