Re: Copying data from a table to another database

On Sun, 2008-05-25 at 12:24 +0100, Pedro Doria Meunier wrote:

> Now I've migrated the entire 'shebang' to another server and need to
> update the history table on the new server with data from the old
> server
> for every different record.

I'm not sure I understand this completely. Would COPY work? For example:

$ psql -h <oldhost> -d <olddb> -c 'copy <schema>.<table> to stdout' |
psql -h <newhost> -d <newdb> -c 'copy <schema>.<table> from stdin'

This presumes that a table with the same structure already exists in the
new database.

If you have made schema changes, or you need only a subset of rows, you
can specify an appropriate select statement to the copy command on "old"
database. See documentation for COPY.

Also consider a statement like this:
=> INSERT INTO newtable SELECT * FROM oldtable EXCEPT SELECT * FROM
newtable;
I'm assuming that you populate a temporary oldtable in the new db
(perhaps using the COPY method above). This won't work if there are
intentional identical rows in your table.

The pipe assumes a Unix-ish box.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0