Re: Copying data from a table to another database

[Reece Hart <reece(at)harts(dot)net>]

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