CODEWITHSUNDEEP
sqlpostgresql
StoryTeller
StoryTeller

Reputation: 1748

Postgres: How do I insert from another table and set a foreign key to the row afterwards?

I want to migrate my database structure and extract some fields in other tables.

I have a table orders:

+---------+--------------+------------+
| orderId | shipmentcost | shipmentId |
+---------+--------------+------------+
| 1       | 9.9          |null        |
| 2       | 4.9          |null        |
+---------+--------------+------------+

in which I just added shipmentcost.shipmentId so I can create a new table shipments:

+------------+--------------+
| shipmentId | shipmentcost |
+------------+--------------+

This table is empty and you can probably guess I want to connect it to orders and fill it with the data now included in orders so I can delete the column orders.shipmentcost

The result of the query should be:

+---------+--------------+------------+
| orderId | shipmentcost | shipmentId |
+---------+--------------+------------+
| 1       | 9.9          |1           |
| 2       | 4.9          |2           |
+---------+--------------+------------+

+------------+--------------+
| shipmentId | shipmentcost |
+------------+--------------+
| 1          | 9.9          |
| 2          | 4.9          |
+------------+--------------+

Both orders.orderId and shipment.shipmentId are the primary key and filled with the sequences orders_orderId_seq as shipments_shipmentId_seq

So how do I achieve inserting the data of orders into shipments while updating orders.shipmentId?

I am using PostgeSQL 9.2, although it would be appreciated if the solution is compatible with other Postgres versions as well

Upvotes: 2

Views: 102

Answers (1)

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125244

Temporarily add the orderId column to shippments:

alter table shippments
add column orderId integer;

Insert into it using the returned orderId to update the orders table

with i as (
    insert into shippments (shippmentcost, orderId)
    select shippementcost, orderId
    from orders
    returning shippementId, orderId
)
update orders o
set shippmentId = i.shippmentId
from i
where i.orderId = o.orderId
;

Drop the temporary column:

alter table shippments drop column orderId;

And the now unnecessary orders.shippmentCost column:

alter table orders drop column shippmentCost;

Upvotes: 2

Related Questions