Re: COPY FROM - how to identify results? - Mailing list pgsql-general

I agree that with a temp table, the portfolio_id could be cleanly
inserted as you suggest, from the temp table into the staging table. The
staging table would need a portfolio_id, since it could house data from
several different spreadsheets at the same time. In fact, the staging
table could be bypassed altogether, it would add little value to "copy"
to "temp", "insert" to staging, "insert" to main. Having the "staging"
table be a temp table would seem to be a general solution in these cases
where several users could be uploading files.

Barring a new version of COPY, I think this is the best solution.

Thank you
Jaime

John D. Burger wrote:
>> nextval() and sequences are not what I'm looking for. I want to
>> assign the same id to all the rows imported from the same file. Let's
>> say user A is working on portfolio_id 3, and decides to upload a
>> spreadsheet with new values. I want to be able to import the
>> spreadsheet into the staging table, and assign a portfolio_id of 3 to
>> all its entries.
>> Of course, I can't just UPDATE the staging table to have portfolio_id
>> = 3, because user B might also be uploading a sheet for portfolio_id
>> = 9.
>
> The first thing to occur to me is to make the staging table TEMP, so
> every session its own copy.  But the second thing is, do you really
> need a portfolio_id column in the staging table?  After you get the
> data massaged correctly into the staging table, perhaps you could load
> it into the main table thusly:
>
>   insert into main_table (portfolio_id, other_columns ...)
>     select 3, other_columns ... from staging_table;
>
> where 3 is the portfolio_id you want to assign to all the data you're
> currently loading.  This may not work exactly for your situation, but
> does some variant make sense?
>
> - John Burger
>   MITRE
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org/
>


***********************************************************************
Bear Stearns is not responsible for any recommendation, solicitation,
offer or agreement or any information about any transaction, customer
account or account activity contained in this communication.

Bear Stearns does not provide tax, legal or accounting advice.  You
should consult your own tax, legal and accounting advisors before
engaging in any transaction. In order for Bear Stearns to comply with
Internal Revenue Service Circular 230 (if applicable), you are notified
that any discussion of U.S. federal tax issues contained or referred to
herein is not intended or written to be used, and cannot be used, for
the purpose of:  (A) avoiding penalties that may be imposed under the
Internal Revenue Code; nor (B) promoting, marketing or recommending to
another party any transaction or matter addressed herein.
***********************************************************************