Thread: Re: best method to copy data across databases

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

reina_ga@hotmail.com (Tony Reina) writes:
> 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.

In recent versions (definitely 7.4, don't remember about 7.3),
pg_dump will include a column list in its COPY commands, so a
plain pg_dump should work.  The way with COPY will be a good bit
faster than a pile of INSERT commands.
        regards, tom lane

Many thanks Tony and Tom,

since this was a "one time" process speed wasn't an issue...
I just did a plain pg_dump with insert and explicit column names in
the dump, then used vi to rename the columns and get rid of some of
them...
non very scientific but it worked :)

cheers.

tgl@sss.pgh.pa.us (Tom Lane) wrote in message news:<14059.1088777527@sss.pgh.pa.us>...
> reina_ga@hotmail.com (Tony Reina) writes:
> > 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.
> 
> In recent versions (definitely 7.4, don't remember about 7.3),
> pg_dump will include a column list in its COPY commands, so a
> plain pg_dump should work.  The way with COPY will be a good bit
> faster than a pile of INSERT commands.
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match