如何将外键相关的项插入到主表中?

huangapple go评论109阅读模式
英文:

How to insert foreign key related items to main table?

问题

我正在尝试重新调整/修改表格。我需要创建一个新表,并将相关表格列中的数据添加到主表中,基本上是将数据从引用表移动到主表中。

这是主表,你可以看到它有一个image_pathes数组列。
如何将外键相关的项插入到主表中?

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

这是第二个表,它有一个image_path列,pryomka_id是外键,引用了mainone表的id。
如何将外键相关的项插入到主表中?

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

我想要的是从referencing表中获取image_path列的数据,并将其追加到相关的mainone表中的image_paths数组列中。例如:

获取referencing.pryomaka_id = mainone.id的image_path
并将其追加到mainone.image_paths中
英文:

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 

答案1

得分: 1

以下是翻译好的内容:

类似这样的代码应该可以工作:

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;
英文:

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;

huangapple
  • 本文由 发表于 2023年7月4日 15:56:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/76610456.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定