How to perform multiple inserts into postgresql database in one query

I have an orders, recipient and items tables.

I want to insert data into each of these tables based on the response from the previous query.

insert into recipient(name, address) values(name, address);
insert into orders(recipient_id, total_price, total_quantity) values(recipient_id, 2000, 20);
insert into items(order_id, item, price, quantity, total) values(order_id, item1, 230, 2, 260);
insert into items(order_id, item, price, quantity, total) values(order_id, item2, 500, 2, 1000);

I want to execute this in a single PostgreSQL query.

This is what I have tried

insert into users (fullname, email, phone, address, status)
  values ('Mine Essien', 'davidessienshare@gmail.com', '08167462431', 'Andela Epic tower Lagos')
  returning id as recipient_id
)

new_order AS(
  insert into orders(user_id, recipient_id, quantity, total_cost)
  values(1, recipient_id, 5, 9500)
  returning id as order_id
)
new_item as (insert into items(order_id, item, quantity, price, total) values(order_id, 'Pepper soup', 2, 2500, 5000), values(order_id, 'Ishiewu', 1, 5000, 5000), values(order_id, 'Fried yam', 2, 2000, 4000);

It seems your tables are defined with a serial or identity column and you want to use the generated value from one insert as a foreign key in the next statement. This can indeed be done using a data modifying CTE combined with the returning clause:

with new_recipient as (
  insert into recipient
    (name, address) 
  values
    (name, address)
  returning recipient_id
), new_order as (
  insert into orders
     (recipient_id, total_price, total_quantity) 
  values
     ((select * from new_recipient), 2000, 20)
  returning order_id
)
insert into items 
  (order_id, item, price, quantity, total) 
values
  ((select * from new_order), 'item1', 230, 2, 260), 
  ((select * from new_order) , 'item2', 500, 2, 1000);

However this could also be done using three different insert statements:

into recipient
  (name, address) 
values
  (name, address);

insert into orders
   (recipient_id, total_price, total_quantity) 
values
   (lastval(), 2000, 20);

insert into items 
  (order_id, item, price, quantity, total) 
values
  (lastval(), 'item1', 230, 2, 260), 
  (lastval(), 'item2', 500, 2, 1000);

The function lastval() returns the most recently generated sequence number. So in the second insert that would be the recipient_idand in the third statement it would be theorder_id`.

StackExchange Database Administrators Q#218234, answer score: 12