在单个查询中插入 2 个表 postgresql
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29564179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
INSERT in single query into 2 tables postgresql
提问by user2938332
I have this 3 tables.
我有这3张桌子。
Employee
PK : id
name
completedBy
FK : employee_id
FK : order_id
Order
PK : id
date
I created form for creating order where i fill infos of order (date) and who completed order. In my form there is a table from which I select the employee and get his id. I want to know if there is possible to insert into tables Order and completedBy with one single query.
我创建了用于创建订单的表单，我在其中填写订单信息（日期）和完成订单的人。在我的表单中有一个表格，我可以从中选择员工并获取他的 ID。我想知道是否可以通过一个查询将 Order 和 completedBy 插入表中。
Is there any difference in effectivity between using two inserts or using the code in answer ?
使用两个插入或使用答案中的代码在效率上有什么区别吗？
回答by a_horse_with_no_name
This can be done using a data modifying common table expression:
这可以使用数据修改公用表表达式来完成：
with new_order as (
insert into orders (id, date) values (1, current_date)
returning id
)
insert into completedby (employee_id, order_id)
values
( 42 -- employee_id,
(select id from new_order)
);
The first part inserts into the orderstable and returns the ID that was inserted. The second part then inserts the row into the completedbytable using the known employee_id and retrieving the order_id from the previous step.
第一部分插入到orders表中并返回插入的 ID。然后第二部分completedby使用已知的employee_id 并从上一步中检索order_id将行插入到表中。
Edit
编辑
if the idcolumn in the orderstable is a serialcolumn and you want to let the sequence generate the value you can do that as well:
如果id在列orders表是一个serial列，你想让序列生成，你可以做到这一点，以及价值：
with new_order as (
insert into orders (date) values (current_date)
returning id
)
insert into completedby (employee_id, order_id)
values
( 42 -- employee_id,
(select id from new_order)
);
