Re: Bulk inserts into two (related) tables - Mailing list pgsql-general

Rich:

On Wed, May 22, 2019 at 2:40 PM Rich Shepard <rshepard@appl-ecosys.com> wrote:
> > I'm curious, what org_id do you put (manually) to the people? you must
> > have some way to match it ( like, say, "I have an organization line,
> > followed by lines for people in this organization"

> The first data entered was in small chunks so I'd add a few rows to the
> organization table, output a file of org_id and org_name, then use the
> source data to associate that org_id to the people associated with it.

This is my point, if you explain how you "use the source data to
assiociate that org_id to the people" I ( the list in general ) MAY be
able to suggest something to help you.

But, I do not know how you do that association step. You may have the
them in an original directory, may have it commited to memory, may
have people typed in green cards attached with red strings to
organizations carved in wooden blocks. And this information is
crucial.

> With this new data source I want to populate the two tables more quickly. I
> think the time consuming part is associating people with their organization.
> I'll do more thinking about this.

Some sample lines may be useful, even with made up data, structure is
what is important. I.e., something like
>>
O "clavelitos locos" 24 37 82
O "bits cuadrados" 36 0 27
P "Oscar Ortega", 46, "manager", "clavelitos locos"
P "Abel Alonso", 37, "boss", "bits cuadrados"
P "Pepe Perez", 52, null, "clavelitos locos"
<<
From this ( and a description of the type of ids in your tables, i.e.,
are they sequences? texts? hashes? uuids? ) I could suggest something
like "put them into two temp tables, add org_id to Os, join in the
appropiate column to put org_id in P, slice them with a select and
send data to final table".

Or, from something like this:
>>
clavelitos_locos 24 37 82
 Pepe_Perez 52 \N
 Oscar_Ortega 46 manager
bits_cuadrados 36 0 27
  Abel_Alonso 37 boss
<<
( I've used single spaces instead of the usual tabs for separators in
the example )
I could suggest "pipe it through this, use your start id instead of
1200": perl -ne 'BEGIN{$o=1200}; if (/^\s/) { print STDERR "$o$_"; }
else { ++$o; print "$o $_"; }' > orgs 2>people
( I would add a little explaining, in case it sounds like line noise )
Which will give you:
>>
0:~$ fgrep '' orgs people
orgs:1201 clavelitos_locos 24 37 82
orgs:1202 bits_cuadrados 36 0 27
people:1201 Pepe_Perez 52 \N
people:1201 Oscar_Ortega 46 manager
people:1202  Abel_Alonso 37 boss
<<
Which could easily be copied in, perhaps with an intermediate table
for a little clean up.



That's why I pointed to xyproblem, I think you are not stating your
problem correctly. I've done thousands of things like this over the
years, I they are normally not that hard.

Regards.
   Francisco Olarte.











>
> I don't do perl, though.
>
> Thanks,
>
> Rich
>
>