sqlpostgresqlgopgxsqlc

How to insert foreign key related items to main table?


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. mainone

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. enter image description here

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 

Solution

  • 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;