From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Rich Shepard <rshepard(at)appl-ecosys(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Bulk inserts into two (related) tables |
Date: | 2019-05-21 17:03:09 |
Message-ID: | ac5b9b47-77ec-29f2-9b23-fe02f9b6c673@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/21/19 9:56 AM, Rich Shepard wrote:
> I'm cleaning and formatting a 800-line data file to be inserted into a
> database. Some of the input file fields will be inserted into an
> 'organizations' table letting postgres assign sequential org_id numbers.
> Other fields will be inserted into a separate 'people' table associated
> with
> each organization. The people table insert allows postgres to assign the
> person_id number and this table's foreign key, people.org_id ==
> organizations.org_id.
>
> I could insert all new rows into the organizations table, then produce a
> list of the org_id and org_name to manually insert the foreign key in the
> related people table. Time and effort intense.
>
> Is there a way to insert the two tables sequentially without manually
> adding
> the organizations.org_id to the appropriate foreign key column
> (people.org_id)
> of the people table?
Well you are not going to know the org_id until the organization table
is loaded, which means something like:
1) First run through file load the organizations table.
2) Build a mapping of org_id to organization.
3) Run through data file again and load people data using the mapping in
2) to provide the people.org_id.
>
> Rich
>
>
--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2019-05-21 17:05:11 | Re: Re: Refresh Publication takes hours and doesn´t finish |
Previous Message | Rich Shepard | 2019-05-21 16:56:38 | Bulk inserts into two (related) tables |