Insert values from another table and update original table with returning values

Question

I'm new to PostgreSQL (and even Stackoverflow).
Say, I have two tables Order and Delivery:

Order

id     product      address           delivery_id
--------------------------------------------------
1      apple        mac street        (null)
3      coffee       java island       (null)
4      window       micro street      (null)

Delivery

id     address
----------------

Delivery.id and Order.id are auto-incrementing serial columns.
The table Delivery is currently empty.

I would like to move Order.address to Delivery.address and its Delivery.id to Order.delivery_id to arrive at this state:

Order

id     product      address           delivery_id
--------------------------------------------------
1      apple        mac street        1
5      coffee       java island       2
7      window       micro street      3

Delivery

id     address
---------------------
1      mac street
2      java island
3      micro street

I'll then remove Order.address.

I found a similar question for Oracle but failed to convert it to PostgreSQL:

• How to insert values from one table into another and then update the original table?

I still think it should be possible to use a plain SQL statement with the RETURNING clause and a following INSERT in Postgres.

I tried this (as well as some variants):

WITH ids AS (
    INSERT INTO Delivery (address)
    SELECT address
    FROM Order
    RETURNING Delivery.id AS d_id, Order.id AS o_id
)
UPDATE Order
SET Delivery_id = d_id
FROM ids
WHERE Order.id = ids.o_id;

This latest attempt failed with:

ERROR: missing FROM-clause entry for table "Delivery" LINE 1: ...address Order RETURNING Delivery.id...

How to do this properly?

Answer 1

First of all, ORDER is a reserved word. Don't use it as identifier. Assuming orders as table nae instead.

WITH ids AS (
   INSERT INTO delivery (address)
   SELECT DISTINCT address
   FROM   orders
   ORDER  BY address -- optional
   RETURNING *
   )
UPDATE orders o
SET    delivery_id = i.id
FROM   ids i
WHERE  o.address = i.address;

You have to account for possible duplicates in order.address. SELECT DISTINCT produces unique addresses.

In the outer UPDATE we can now join back on address because delivery.address is unique. You should probably keep it that way beyond this statement and add a UNIQUE constraint on the column.

Effectively results in a one-to-many relationship between delivery and orders. One row in delivery can have many corresponding rows in orders. Consider to enforce that by adding a FOREIGN KEY constraint accordingly.

This statement enjoys the benefit of starting out on an empty delivery table. If delivery wasn't empty, we'd have to work with an UPSERT instead of the INSERT. See:

• How to use RETURNING with ON CONFLICT in PostgreSQL?

Related:

• Insert data in 3 tables at a time using Postgres

About the cause for the error message you got:

• RETURNING causes error: missing FROM-clause entry for table

Use legal, lower-case identifiers exclusively, if you can. See:

• Are PostgreSQL column names case-sensitive?