英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论