CODEWITHSUNDEEP
sqlpostgresql
pr00thmatic
pr00thmatic

Reputation: 825

using the ids returned from insert into, for record insertion with foreign key

I have a table

monster(id serial, name varchar, primary key(id))

and i have another table

ranged_monster(id_monster integer, distance integer, foreign key(id_monster) references monster)

I want to insert two ranged monsters: one is called 'archer goblin' with a range attack distance of 10, and the another is called 'dragon' with a range attack distance of 50. How can i do this in one instruction?

so far, i have tried this:

the worst way

insert into monster(name) values ('archer goblin'), ('dragon');

insert into ranged_monster(distance) values (10) where name='archer goblin';

insert into ranged_monster(distance) values (50) where name='dragon';

it is bad because the name column allows repetitions, and the retrieved records might be more than one... also having to write the name of the monster twice does not seems like a good habit.

insert into ... returning

if the table ranged_monster only had the column (foreign key) of the id_monster, then i could use this solution:

with the_ids as (
    insert into monster(name) 
    values ('archer goblin'), ('dragon') 
    returning id
) 
insert into ranged_monster(id_monster) 
    select * from the_ids;

however it does not work, because ranged_monster also has the column distance. Doing so, will insert the ids of the monsters without distance.

possible solutions

create a temporal table with the distances, and then combine this temporal table sequentially with the insert into ... returning's the_ids, and then insert this combined records into the ranged_monster table.

How can i combine two tables as asked in here https://stackoverflow.com/questions/31171253/sql-combine-two-tables ? (it was marked as duplicated, linking to this What is the difference between JOIN and UNION? , but that question is not related to that another question.)

Upvotes: 2

Views: 660

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

with s(name, distance) as (
    values ('archer goblin', 10), ('dragon', 50)
), the_ids as (
    insert into monster(name) 
    select name
    from s
    returning id, name
)
insert into ranged_monster (id_monster, distance)
select id, distance
from
    s
    inner join
    the_ids using (name)

Upvotes: 1

Related Questions