Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INSERT in single query into 2 tables postgresql

I have this 3 tables.

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.

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

like image 413
user2938332 Avatar asked Apr 10 '15 14:04

user2938332


People also ask

Can we insert data in two tables using single query?

You can't use INSERT against two tables in one statement.

How do you insert data from multiple tables into one table in Postgres?

If multiple rows can come with identical (firstname, lastname) , you may need to fold duplicates for the first INSERT : ... INSERT INTO sample (firstname, lastname) SELECT DISTINCT firstname, lastname FROM data ... You could use a (temporary) table as data source instead of the CTE data .


1 Answers

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 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.

Edit

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)
);
like image 151
a_horse_with_no_name Avatar answered Sep 22 '22 11:09

a_horse_with_no_name