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:
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?
First of all,
ORDER
is a reserved word. Don't use it as identifier. Assumingorders
as table nae instead.You have to account for possible duplicates in
order.address
.SELECT DISTINCT
produces unique addresses.In the outer
UPDATE
we can now join back onaddress
becausedelivery.address
is unique. You should probably keep it that way beyond this statement and add aUNIQUE
constraint on the column.Effectively results in a one-to-many relationship between
delivery
andorders
. One row indelivery
can have many corresponding rows inorders
. Consider to enforce that by adding aFOREIGN KEY
constraint accordingly.This statement enjoys the benefit of starting out on an empty
delivery
table. Ifdelivery
wasn't empty, we'd have to work with an UPSERT instead of theINSERT
. See:Related:
About the cause for the error message you got:
Use legal, lower-case identifiers exclusively, if you can. See: