Re: Bulk inserts into two (related) tables

From: Rich Shepard <rshepard(at)appl-ecosys(dot)com>
To: "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Bulk inserts into two (related) tables
Date: 2019-05-22 19:24:44
Message-ID: alpine.LNX.2.20.1905221218100.1484@salmo.appl-ecosys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Wed, 22 May 2019, Adrian Klaver wrote:

> A sample of the data you are cleaning up.

Adrian, et al.:

I have it working properly now. Both org_id and person_id numbers are
prepended to each row in the appropriate table and they are unique because
each series begins one greater than the max(*_id) in each table.

> I think what people are trying to wrap there head around is how 800 lines
> in the file is being split into two subsets: the organization data and the
> people data. In particular how that is being done to preserve the
> relationship between organizations and people? This is before it ever gets
> to the database.

After cleaning there are 655 lines rather than 800, but the short answer is
that spliting the data preserves the relationship between organization and
its people:

#!/usr/bin/gawk

# Read input file, write fields to both organizations and people
# input files.

BEGIN { FS=OFS="," }
# for organizations table input:
{ print $1, $2, $4, "," $8, $9, $10, "'US'," $11, ",,," "'Opportunity','');" > "z-orgs.sql" }
# for people table input:
{ print $6, $5, "," $1, $3, $4, $5, $7, $8, $9, $10, "'US'," $11, "," $12, "," $13, "'true','');" > "z-people.sql" }

You can see that the org_id field ($1) is used in both files, but in
different columns in the two tables.

Regards,

Rich

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Perumal Raj 2019-05-22 21:18:22 Centos : Load Average :OS Process Name : migration/1, migration/2 , migration/n
Previous Message Jeremy Finzel 2019-05-22 19:13:07 Re: Bulk inserts into two (related) tables