Re: best method to copy data across databases - Mailing list pgsql-sql

ctrl@altonsys.com (ctrl) wrote in message news:<46a31c4d.0407011942.37839bb0@posting.google.com>...
> I need to copy data that I have on a table in one Postgres database
> into another table, on a different database. The destination table is
> not identical with the source table so I need to do some mapping
> between the 2 tables.
> What would be the best (and quickest) way to transfer the data? (there
> are over 500k rows)
> 
> thanks!

If the 2 tables have different arrangements, then I'm not sure if
there is a quick way. The safest way is probably to do a pg_dump
--attribute-inserts.

From the pg_dump manpage,

--attribute-inserts
             Dump  data as INSERT commands with explicit column names
(INSERT             INTO table (column, ...) VALUES ...). This will make
restoration             very  slow,  but  it is necessary if you desire to
rearrange the             column ordering.

HTH,
-Tony