amoe amoe - 1 year ago 75
SQL Question

PostgreSQL: Extract column into separate table and update other column with related key

I have a table

with column
. These parts of the data need to be moved to a related table,
, which has a separate column
and a surrogate key
. The tables are related through the foreign key
. Is there a way to insert all values of
into the
table and immediately update the
to refer to the rows that were just created?

This has to be done in SQL or PL/pgSQL as it is part of a series of database migrations, which do not allow arbitrary scripting.

id | product_image_path
1 | foo.jpg
2 | bar.jpg
3 | foo.jpg

Should become:

id | image_id
1 | 1
2 | 2
3 | 3

id | image_path
1 | foo.jpg
2 | bar.jpg
3 | foo.jpg

Answer Source

If the new image id can be the same as the product id, this is quite easy:

Create the new table:

create table image (id serial primary key, image_path text);

Copy the data from the product table:

insert into image (id, image_path)
select id, product_image_path
from product;

Adjust the sequence for the column:

select setval(pg_get_serial_sequence('image', 'id'), (select max(id) from image));

Add the new image_id column and populate it:

alter table product add image_id integer;
update product set image_id = id;

Get rid of the old_column:

alter table product drop product_image_path;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download