Re: Table transfer

On Tue, Sep 21, 2010 at 5:28 PM, Jorge Alberto Fuentes Casillas
<buen_sama(at)yahoo(dot)com(dot)mx> wrote:
>     Currently i have 2 databases in my postgresql server, and i want to
> transfer the data from my db1.table1 to my db2.table2.

The following is all assuming that you're actually talking about
separate "databases" as you claim, and not "schemas" as your SQL
syntax suggests.

Databases within a PostgreSQL cluster are essentially isolated[1] from
one another, so you're not going to be able to accomplish this data
transfer with a single query. I think the easiest solution is to
pg_dump db1.table1, then restore that table into db2. From there, it
should be pretty easy for you to load your data into table2. Quick
example:
pg_dump -Fc --table=schema1.table1 --file=table1.pgdump db1
pg_restore --dbname=db2 table1.pgdump

And once that's done it should be pretty easy for you to construct an
INSERT statement, something like:
INSERT INTO schema1.table1 (row1, row2, ...) SELECT row1, row2, ...
FROM schema1.table2;

Josh

[1] There's a contrib module called dblink to let different databases
talk to each other, but I suspect you'll have more trouble getting it
to work for your case than a simple dump and reload.