That's sort of what I have already, and my problem is that the
portfolio_id field does not exist in the CSV files. I'd like to be able
to assign a portfolio_id, for the current file's entries. Another person
in the list suggested dynamically adding a column with the portfolio_id
to the file, and that of course would work, but is kinda messy.
The problem with the solution you suggest is that when doing COPY, I'll
get a complaint because of trying to populate an entry with a null value
for portfolio_id.
Some sort of automatic population of the portfolio_id field wouldn't
work either, since many different users and processes could be inserting
data into the staging table simultaneously.
Seems like you need to adjust your schema to use a pivot table:
CREATE TABLE portfolio (
id SERIAL PRIMARY KEY,
...
CREATE TABLE portfolio_entries (
portfolio_id INT4 NOT NULL,
...
CONSTRAINT fk_portfolio_entries FOREIGN KEY (portfolio_id)
REFERENCES portfolio
ON DELETE CASCADE
Then you should be able to insert directly into the second table a row
for each entry (for want of a better word) that corresponds to a
particular portfolio.
brian
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
***********************************************************************
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.
***********************************************************************
