[SOLVED] in flyway, is there a way to read the inserted rows id and use it to insert into another table?

in flyway, is there a way to read the inserted rows id and use it to insert into another table?

If we have two tables and want to insert some rows for static data into flyway, we could do it like this:

Insert into country (id, iso) values (10, "GBR);
Insert into player (name, country_id) values ("bob", 10);

Given that country has a serial4 id, and a sequence, this will mean that the sequence nextval wont be right. The usual practice is to do something like

setval("country_id_seq", 10000);

but it would be better if we could get the ID of the inserted value and use it on the next insert, which would use the id sequences correctly.

Does flyway or sql have a solution for getting the id of a row inserted with serial4 (aka autoincrement) id, and use it later?

kind of like this:

id1 = Insert into country (id, iso) values (10, "GBR);
Insert into player (name, country_id) values ("bob", ${id1});

Solution

Here is a solution using a data-modifying CTE, I believe self explanatory. BGR and Stefan are the parameters. Temporary tables country and player are illustrative.

create temporary table country (id serial, iso text);
create temporary table player (country_id integer, name text);

with t(id) as (
  insert into country(iso) values ('BGR') 
  returning id
)
insert into player (country_id, name)
select id, 'Stefan' from t;

select * from player; -- to see the result