Re: making a copy of a table within the same database

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(at)lfix(dot)co(dot)uk>
LFIX Ltd