Skip site navigation (1) Skip section navigation (2)

Re: Partial table copy?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Mark Thomas <thomas(at)pbegames(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Partial table copy?
Date: 2002-05-08 20:43:35
Message-ID: 12363.1020890615@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Mark Thomas <thomas(at)pbegames(dot)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

In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2002-05-08 20:44:52
Subject: Re: Too many clients
Previous:From: Sharon CowlingDate: 2002-05-08 20:01:00
Subject: Re: Incorrect Query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group