Thread: making a copy of a table within the same database
I wanted to dump the contents of one table to another (with a different name) within the same database. I am looking at pg_restore and it doesn't seem to have the option of specifying the name of the table we want to dump to, only the name we want to dump from. Does this mean I have to create the table and do an sql statement to copy the table? Is this the best way? Sally _________________________________________________________________ Fast. Reliable. Get MSN 9 Dial-up - 3 months for the price of 1! (Limited-time Offer) http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/
Hello select into is usefull cmd for you http://developer.postgresql.org/docs/postgres/sql-selectinto.html select * into newtable from oldtable; regards Pavel Stehule On Wed, 3 Mar 2004, Sally Sally wrote: > I wanted to dump the contents of one table to another (with a different > name) within the same database. I am looking at pg_restore and it doesn't > seem to have the option of specifying the name of the table we want to dump > to, only the name we want to dump from. Does this mean I have to create the > table and do an sql statement to copy the table? Is this the best way? > Sally > > _________________________________________________________________ > Fast. Reliable. Get MSN 9 Dial-up - 3 months for the price of 1! > (Limited-time Offer) http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On 2004.03.03 09:00 Sally Sally wrote:
> I wanted to dump the contents of one table to another (with a
> different name) within the same database. I am looking at pg_restore
> and it doesn't seem to have the option of specifying the name of the
> table we want to dump to, only the name we want to dump from. Does
> this mean I have to create the table and do an sql statement to copy
> the table? Is this the best way?
You might want to look at SELECT INTO ... .
Karl <kop@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
Sally Sally wrote: > I wanted to dump the contents of one table to another (with a different > name) within the same database. I am looking at pg_restore and it > doesn't seem to have the option of specifying the name of the table we > want to dump to, only the name we want to dump from. Does this mean I > have to create the table and do an sql statement to copy the table? Is > this the best way? If you want a copy of the data and the fundamental table design, not including constraints, triggers, indexes, foreign keys, etc.: CREATE TABLE foo AS SELECT * FROM bar; ... Mike Mascari
Karl O. Pinc wrote: > > On 2004.03.03 09:00 Sally Sally wrote: > >> I wanted to dump the contents of one table to another (with a >> different name) within the same database. I am looking at pg_restore >> and it doesn't seem to have the option of specifying the name of the >> table we want to dump to, only the name we want to dump from. Does >> this mean I have to create the table and do an sql statement to copy >> the table? Is this the best way? > > > You might want to look at SELECT INTO ... . > > Karl <kop@meme.com> > Free Software: "You don't pay back, you pay forward." > -- Robert A. Heinlein > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster See http://www.postgresql.org/docs/7.4/static/sql-selectinto.html http://www.postgresql.org/docs/7.4/static/sql-createtableas.html for more info. Nick
create table newtable as select * from oldtable; :) Sally Sally wrote: > I wanted to dump the contents of one table to another (with a > different name) within the same database. I am looking at pg_restore > and it doesn't seem to have the option of specifying the name of the > table we want to dump to, only the name we want to dump from. Does > this mean I have to create the table and do an sql statement to copy > the table? Is this the best way? > Sally > > _________________________________________________________________ > Fast. Reliable. Get MSN 9 Dial-up - 3 months for the price of 1! > (Limited-time Offer) > http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/ > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
On Wed, 2004-03-03 at 15:00, Sally Sally wrote:
> I wanted to dump the contents of one table to another (with a different
> name) within the same database. I am looking at pg_restore and it doesn't
> seem to have the option of specifying the name of the table we want to dump
> to, only the name we want to dump from. Does this mean I have to create the
> table and do an sql statement to copy the table? Is this the best way?
There are several ways to do it, depending on what you want.
You can create a new table (with no constraints):
SELECT * INTO new_table FROM old_table;
Or create the new table with any necessary constraints, then:
INSERT INTO new_table SELECT * FROM old_table;
Or dump to text and edit the dump file to change all occurrences of the
table name:
pg_dump -d my_database -t old_table > dump.sql
vi dump.sql
psql -d my_database < dump.sql
or edit on the fly (if the old table name doesn't occur except as a
table name):
pg_dump -d my_database -t old_table |
sed -e 's/old_table/new_table/g' |
psql -d my_database
--
Oliver Elphick <olly@lfix.co.uk>
LFIX Ltd