psql move data to another table leaving refference to new row

Question

So, i`m writing migration.

My goal is to:

1. Create another table, which contains all the necessary fields
2. Move existing data from the source table to the dest one. (insert data into the dest, then update reference in the source, setting the dest_id as id of the just created row in the dest table)
3. Delete the rows which were moved from the source table.

I`m having difficulties with the step 2. Any ideas?

I currently have this:

do $$
declare os_id bigint;
begin
    insert into order_shipments (order_id, method_id, deliver_at, barcode, sent_at, city_id, subway_id)
    select o.id, o.shipment_method_id, o.courier_deliver_at, o.barcode, o.sent_at, o.courier_city_id, o.courier_subway_id
    from orders o
    returning id into os_id;
end
$$;

update orders as o set current_shipment_id = os_id;

I'm creating anonymous block, which takes data from orders (source) and inserts it into the order_shipments (dest).

Answer 1

Just found rather simple solution.

Thanks to the to the question Insert data and set foreign keys with Postgres

The result is

with i as (
    insert into order_shipments (order_id, method_id, deliver_at, barcode, sent_at, city_id, subway_id)
    select o.id, o.shipment_method_id, o.courier_deliver_at, o.barcode, o.sent_at, o.courier_city_id, o.courier_subway_id
    from orders o
    returning id
)
update orders o
set current_shipment_id = i.id
from i;