I'm trying to reshape/alter table. I have to create a new table and add data from related table column to main table, basically moving data from reference to main.
Here is the main table as you can see it has image_pathes array column.
CREATE TABLE mainone (
id bigint NOT NULL,
task_id bigint NOT NULL,
object_created_at character varying(191) NOT NULL,
closed_at character varying(191) NOT NULL,
object_name text NOT NULL,
region_id integer NOT NULL,
district_id integer NOT NULL,
customer_inn bigint NOT NULL,
customer_name character varying(191) NOT NULL,
response json NOT NULL,
image_pathes character varying(191) ARRAY,
created_at timestamp(0) without time zone,
updated_at timestamp(0) without time zone
);
And here is the second table it has image_path column pryomka_id is foreign key referencing to id of mainone.
CREATE TABLE referencing (
id bigint NOT NULL,
pryomka_id bigint NOT NULL,
image_path character varying(191) NOT NULL,
created_at timestamp(0) without time zone,
updated_at timestamp(0) without time zone
);
What I want is to get data from referencing table in image_path column and append it to related main table and append to image_paths array column. For example
get image_path where referencing.pryomaka_id = mainone.id
and append mainone.image_paths
Something like this should work:
update mainone as m set
image_pathes = s.imgs
from (
select pryomka_id, array_agg(image_path) as imgs from referencing
group by pryomka_id
) as s(pryomka_id, imgs)
where s.pryomka_id = m.id;