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.
***********************************************************************
