making a copy of a table within the same database

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:

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

create table newtable as select * from oldtable;
:)

Sally Sally 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

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

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