在单个查询中插入2个表PostgreSQL [英] INSERT in single query into 2 tables postgresql

问题描述

我有这3张桌子。

Employee
PK : id
name

completedBy
FK : employee_id
FK : order_id

Order
PK : id
date

我创建了用于创建订单的表单，其中填写了订单（日期）的信息以及完成订单的人。在我的表格中，有一个表格可供我选择员工并获取其ID。我想知道是否可以通过一个查询将表Order和completedBy插入表中。

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.

在使用两次插入或在其中使用代码之间，有效性是否存在差异答案？

Is there any difference in effectivity between using two inserts or using the code in answer ?

推荐答案

这可以通过修改公用表表达式的数据来完成：

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

第一部分插入 orders 表并返回插入的ID。然后，第二部分使用已知的employee_id将行插入 completedby 表，并从上一步中检索order_id。

The first part inserts into the orders table and returns the ID that was inserted. The second part then inserts the row into the completedby table using the known employee_id and retrieving the order_id from the previous step.

编辑

如果<$ c中的 id 列$ c> orders 表是一个 serial 列，您希望让序列生成可以执行的值：

if the id column in the orders table is a serial column and you want to let the sequence generate the value you can do that as well:

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

这篇关于在单个查询中插入2个表PostgreSQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！