Re: [GENERAL] 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