Postgres Pro
Downloads
Home   >   mailing lists

Thread: copy table from one database to another

copy table from one database to another

From
John McKown
Date:
Is there a simple way to copy a table from one database to another? I was
totally restructuring the tables in a database, so I simply created a new
database. There was one table in the old database whose information I
wanted. I really wanted to copy the table from the old database to the new
one and rename the table during the copy. What I ended up doing was using
"pg_dump -t table olddb >old.info", then I editted the "old.info" file to
point to the new database and renamed the table as well. I could then use
the "\i old.info" command in psql to import the information. Is there a
better without writing code in Perl or Python? (I.e. within psql).

Thanks,
John

Re: copy table from one database to another

From
Vince Vielhaber
Date:
On Wed, 20 Sep 2000, John McKown wrote:

> Is there a simple way to copy a table from one database to another? I was
> totally restructuring the tables in a database, so I simply created a new
> database. There was one table in the old database whose information I
> wanted. I really wanted to copy the table from the old database to the new
> one and rename the table during the copy. What I ended up doing was using
> "pg_dump -t table olddb >old.info", then I editted the "old.info" file to
> point to the new database and renamed the table as well. I could then use
> the "\i old.info" command in psql to import the information. Is there a
> better without writing code in Perl or Python? (I.e. within psql).

What version of PostgreSQL are you using?  I move tables around all the
time and don't need to edit.  Instead of the \i, try this:

pg_dump -t table olddb > old.info

psql newdb < old.info

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
 128K ISDN from $22.00/mo - 56K Dialup from $16.00/mo at Pop4 Networking
        Online Campground Directory    http://www.camping-usa.com
       Online Giftshop Superstore    http://www.cloudninegifts.com
==========================================================================

Re: copy table from one database to another

From
John McKown
Date:
On Wed, 20 Sep 2000, Vince Vielhaber wrote:

> On Wed, 20 Sep 2000, John McKown wrote:
>
> What version of PostgreSQL are you using?  I move tables around all the
> time and don't need to edit.  Instead of the \i, try this:
>
> pg_dump -t table olddb > old.info
>
> psql newdb < old.info

I'm running PostgreSQL 7.0.2. The reason that I had to edit the pg_dump
output was because I needed to rename the table. That's because I did not
think ahead and I had an empty table in the newdb with the same name as
the one in the olddb. I did not want to replace the table defination. But
now that you mention it, I guess that I could have done an ALTER TABLE
... RENAME TO ... in olddb before doing the pg_dump.

Thanks for the thoughts!
John