CODEWITHSUNDEEP
sqlpostgresql
amoe
amoe

Reputation: 4569

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

Upvotes: 3

Views: 1464

Answers (1)

user330315
user330315

Reputation:

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;

Upvotes: 2

Related Questions