假设这个模式：
create table master (
master_id serial primary key,
project_id int,
category_id int,
supplier_id int
);
create table detail (
detail_id int,
item_id int,
qty numeric(18,2),
rate numeric(18,2),
master_id int references master (master_id)
);
create temporary table temp_detail (
purchase_order_detail_id integer,
item_id integer,
qty numeric(18,2),
project_id integer,
category_id integer,
supplier_id integer,
rate numeric(18,2)
);
可以做到这一点：
with d as (
insert into master (project_id, category_id, supplier_id)
select distinct project_id, category_id, supplier_id
from temp_detail
returning *
)
insert into detail (item_id, qty, rate, master_id)
select item_id, qty, rate, master_id
from
temp_detail td
inner join
d on (td.project_id, td.category_id, td.supplier_id) = (d.project_id, d.category_id, d.supplier_id)
;
602392714
清零编程群