Skip site navigation (1) Skip section navigation (2)

Re: Table transfer

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Jorge Alberto Fuentes Casillas <buen_sama(at)yahoo(dot)com(dot)mx>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Table transfer
Date: 2010-09-22 02:20:03
Message-ID: AANLkTik5R7R+eAbberefAB3gh5fhC3czoS=GKMktfXkN@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

  • Table transfer at 2010-09-21 21:28:05 from Jorge Alberto Fuentes Casillas

pgsql-novice by date

Next:From: Mladen GogalaDate: 2010-09-22 11:39:47
Subject: Re: Table transfer
Previous:From: Leon StarrDate: 2010-09-22 00:06:12
Subject: Re: Best way to create a sequence generator at run time?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group