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
