pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedbest method to copy data across databases
4+ messages / 3 participants
[nested] [flat]
* best method to copy data across databases
@ 2004-07-02 03:42 ctrl <ctrl@altonsys.com>
0 siblings, 1 reply; 4+ messages in thread
From: ctrl@altonsys.com @ 2004-07-02 03:42 UTC (permalink / raw)
To: pgsql-sql
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!
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: best method to copy data across databases
@ 2004-07-02 11:10 Tony Reina <reina_ga@hotmail.com>
parent: ctrl <ctrl@altonsys.com>
0 siblings, 1 reply; 4+ messages in thread
From: reina_ga@hotmail.com @ 2004-07-02 11:10 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: best method to copy data across databases
@ 2004-07-02 14:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tony Reina <reina_ga@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2004-07-02 14:12 UTC (permalink / raw)
To: reina_ga@hotmail.com; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: best method to copy data across databases
@ 2004-07-03 18:19 ctrl <ctrl@altonsys.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: ctrl@altonsys.com @ 2004-07-03 18:19 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2004-07-03 18:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-07-02 03:42 best method to copy data across databases ctrl <ctrl@altonsys.com>
2004-07-02 11:10 ` Tony Reina <reina_ga@hotmail.com>
2004-07-02 14:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-07-03 18:19 ` ctrl <ctrl@altonsys.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox