Postgres-如何使用外键大容量插入表

Question

我想在postgreSQL数据库中进行大容量插入。

• 数据库尚未运行
• postgreSQL 13

我有一个临时暂存表，我批量插入数据

TABLE public.temp_inverter_location
(
id integer ,
inverter_num_in_sld integer,
lift_requirements character varying,
geo_location_id integer NOT NULL (foreign key references geo_location.id),
location_name character varying,
project_info_id integer NOT NULL (foreign key references project_info.id)
)

我正在尝试填充两个外键列temp_inverter_location.geo_location_id和temp_inverter_location.project_info_id。

两个被引用的表由它们的id列引用：

地理位置

CREATE TABLE public.geo_location
(
id integer,
country character varying(50) COLLATE pg_catalog."default",
region character varying(50) COLLATE pg_catalog."default",
city character varying(100) COLLATE pg_catalog."default",
location_name character varying COLLATE pg_catalog."default",
)

和

项目

CREATE TABLE public.project_info
(
id integer 
operation_name character varying,
project_num character varying(10),
grafana_site_num character varying(10)
)

我想将正确的外键填充到列temp_inverter_location.geo_location_id和temp_inverter_location.project_info_id中。

我正在尝试使用INSERT INTO SELECT来用与geo_location.location_name和temp_inverter_location.name匹配的JOIN填充temp_inverter_location.geo_location_id。

我尝试过这个查询，但inverter_location.geo_location_id仍然为空：

INSERT INTO temp_inverter_location(geo_location_id) SELECT geo_location.id FROM geo_location INNER JOIN temp_inverter_location ON geo_location.location_name=temp_inverter_location.location_name;

如果需要更多信息，请告诉我，谢谢！

Answer 1

我能够使用引用另一个表的更新来解决这个问题。

基本上，我使用更新了geo_location_id列

UPDATE temp_inverter_location SET geo_location_id = geo_location.id FROM geo_location WHERE geo_location.location_name = temp_inverter_location.location_name;

并使用更新project_ info_

UPDATE load_table SET project_info_id = project_info.id FROM project_info WHERE project_info.operation_name = load_table.location_name;

这似乎奏效了。