amoe amoe - 1 month ago 9
SQL Question

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

I have a table

product
with column
product_image_path
. These parts of the data need to be moved to a related table,
image
, which has a separate column
image_path
and a surrogate key
id
. The tables are related through the foreign key
product.image_id
. Is there a way to insert all values of
product_image_path
into the
image
table and immediately update the
product.image_id
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.

product:
id | product_image_path
----+-------------------
1 | foo.jpg
2 | bar.jpg
3 | foo.jpg


Should become:

product:
id | image_id
---+---------
1 | 1
2 | 2
3 | 3

image:
id | image_path
---+-----------
1 | foo.jpg
2 | bar.jpg
3 | foo.jpg

Answer

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 image.id 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;