sql - 使用从 insert into 返回的 ids，用外键插入记录

Question

我有一张 table

怪物(id serial, name varchar, primary key(id))

我还有一张 table

ranged_monster(id_monster 整数，距离整数，外键(id_monster)引用怪物)

我想插入两个远程怪物:一个叫'archer goblin'，范围攻击距离为10，另一个叫'dragon ' 的范围攻击距离为 50。我怎样才能在一条指令中做到这一点？

到目前为止，我已经试过了:

最糟糕的方式

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';

这很糟糕，因为名称列允许重复，并且检索到的记录可能不止一条......而且必须将怪物的名称写两次似乎不是一个好习惯。

插入...返回

如果表 ranged_monster 只有 id_monster 的列(外键)，那么我可以使用这个解决方案:

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

但是它不起作用，因为 ranged_monster 也有列 distance。这样做，将无 distance 插入怪物的 id。

可能的解决方案

创建一个包含距离的时态表，然后将这个时态表与 insert into ... returning 的 the_ids 顺序合并，然后插入合并后的记录进入 ranged_monster 表。

我如何按照这里的要求合并两个表 https://stackoverflow.com/questions/31171253/sql-combine-two-tables ？ (它被标记为重复，链接到这个 What is the difference between JOIN and UNION? ，但那个问题不相关另一个问题。)

Answer 1

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)