sql - psql move data to another table leaving refference to new row -

sql - psql move data to another table leaving refference to new row -

so, i`m writing migration.

my goal to:

create table, contains necessary fields move existing info source table dest one. (insert info dest, update reference in source, setting dest_id id of created row in dest table) delete rows moved source table.

i`m having difficulties step 2. ideas?

i have this:

do $$ declare os_id bigint; begin insert 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 orders o returning id os_id; end $$; update orders o set current_shipment_id = os_id;

i'm creating anonymous block, takes info orders (source) , inserts order_shipments (dest).

just found rather simple solution.

thanks to question insert info , set foreign keys postgres

the result

with ( insert 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 orders o returning id ) update orders o set current_shipment_id = i.id i;

sql postgresql migration