Thread: Partial table copy?

I have a table that was created like this:

CREATE SEQUENCE "id_seq" START 1 INCREMENT 1;

CREATE TABLE "players"
(
     "pid" integer PRIMARY KEY DEFAULT nextval('id_seq') NOT NULL,
     "pname" text,
     "value" integer,
);

For testing purposes I'd like to create some test data:

COPY "players" FROM stdin;
1       Bill    8
2       Frank   100
\.

This works, but leaves my sequence out of sync with my actual data. 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?


Mark Thomas
---
thomas@pbegames.com ----> http://www.pbegames.com/~thomas
Play by Electron Games -> http://www.pbegames.com Free Trial Games

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