Mark Thomas <thomas@pbegames.com> writes:
> What I'd really like is:
> COPY "players" FROM stdin;
> nextval('id_seq') Bill 8
> nextval('id_seq') Frank 100
> Obviously that won't work. I guess I can do:
> INSERT INTO "players" (pname, value) VALUES ('Bill', 8);
> INSERT INTO "players" (pname, value) VALUES ('Frank', 100);
> But that seems less intuitive. Is there a better way?
The best way at the moment involves making a temp table that matches
your COPY data, COPYing into the temp table, and then doing
INSERT INTO real-table (columns) SELECT * FROM temp-table
You can do additional manipulation of the data in the SELECT, of
course, so this is a pretty good general-purpose approach for loading
data that needs some massaging.
There is a proposal afoot to allow COPY FROM to specify a column list,
with the specified columns read from the file and the others filled
with defaults. Not sure how soon anyone will get around to doing it.
ISTR someone looking into it a few months ago, but we never got a
completed patch submitted.
regards, tom lane