Postgres - how to bulk insert table with foreign keys

Question

I am looking to do a bulk insert into my postgreSQL database.

database is not yet live
postgreSQL 13

I have a temporary staging table which I bulk inserted data

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

I am trying to populate the two foreign key columns temp_inverter_location.geo_location_id and temp_inverter_location.project_info_id.

The two referenced tables are referenced by their id columns:

geo_location

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",

    )

and

project_info

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

I want to populate the correct foreign keys into the columns temp_inverter_location.geo_location_id and temp_inverter_location.project_info_id.

I am trying to use INSERT INTO SELECT to populate temp_inverter_location.geo_location_id with a JOIN that matches geo_location.location_name and temp_inverter_location.name.

I have tried this query however inverter_location.geo_location_id remains blank:

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;

Please let me know if more info is needed, thanks!

try the select statement till you get all data with joins and what you need and then use the code to replace the select that you have so far, but as nobody knows the layout and the result you want, nobody can help you .see minimal reproducible example — nbk
– nbk, Commented Jul 3, 2021 at 19:36
Hard to say without the schema definitions for the tables involved. Does temp_inverter_location not already have the values for the FK fields? The issue I coudl see is that the tables geo_location and project_info did not exist when you did the INSERT or they exist but the parent value of the FK relationship was not in them. — Adrian Klaver
– Adrian Klaver, Commented Jul 3, 2021 at 19:50
So does temp_inverter_location have values for the geo_location_id and project_info_id? If not then you can't do what you want as you are working from the wrong end of the relationship. What does SELECT geo_location.id FROM geo_location INNER JOIN temp_inverter_location ON geo_location.location_name=temp_inverter_location.location_name; by itself yield? — Adrian Klaver
– Adrian Klaver, Commented Jul 4, 2021 at 0:41
Hi Adrian, yes, the common information they share is in temp_inverter_location.location_name, geo_location_id.location_name and project_info.operation_name. I actually resolved the challenge by using update between two tables with the connecting item being location_name/operation_name as show in postgresqltutorial.com/postgresql-update-join Can I ask what approach you would take? — Doptima
– Doptima, Commented Jul 4, 2021 at 1:33

Doptima · Accepted Answer · 2021-07-06 02:11:55Z

1

I was able to resolve this issue using update referencing another table.

Basically, I updated the geo_location_id column using

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

and updated the project_info_id using

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

It seems to have worked.

edited Jul 6, 2021 at 2:11

answered Jul 4, 2021 at 1:39

Doptima

2011 gold badge3 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Adrian Klaver Over a year ago

I'm not seeing how that helps with project_info_id?

Doptima Over a year ago

Sorry, the answer was incomplete. Here is the project_info_id included.

Collectives™ on Stack Overflow

Postgres - how to bulk insert table with foreign keys

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related