I hadn't even thought of temp tables being limited in scope ... that helps tremendously.
The main caveat is that it is a good idea to drop tt at the end as well.
As for "I don't get why" ... confused questions from novices like me are why this mailing list exists :) ... I clearly
wasn'tthinking right from a Postgres PoV.
One thing that baffles me is that does one just call a function as one would a table when performing the write to STDIN
fromR?
Once I get into it in the next couple of days, I'm sure I'll have specific code questions, but that is a big help.
Thanks again,
Stephen
-----Original Message-----
From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Tuesday, June 20, 2017 5:11 PM
To: Stephen Froehlich <s.froehlich@cablelabs.com>
Cc: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Normalizing Unnormalized Input
On Tue, Jun 20, 2017 at 3:50 PM, Stephen Froehlich <s.froehlich@cablelabs.com> wrote:
> The part of the problem that I haven’t solved conceptually yet is how
> to normalize the incoming data.
The specifics of the data matter but...if at all possible I do something like:
BEGIN
CREATE TEMP TABLE tt
COPY tt FROM STDIN
INSERT NEW RECORDS into t FROM tt - one statement (per target table) UPDATE EXISTING RECORDS in t USING tt - one
statement(per target table) END
I don't get why (or how) you'd "rename the table into a temp table"...
Its nice that we've add upsert but it seems more useful for streaming compared to batch. At scale you should try to
avoidcollisions in the first place.
Temporary table names only need to be unique within the session.
The need for indexes on the temporary table are usually limited since the goal is to move large subsets of it around
allat once.
David J.
