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

Question

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

Add some sample table data, both before and after versions. (As well formatted text.) — jarlh
– jarlh, Commented Oct 28, 2016 at 8:09

user330315 · Accepted Answer · 2016-10-28 08:53:46Z

2

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;

answered Oct 28, 2016 at 8:53

user330315

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related