Postgres Pro
Downloads
Home   >   mailing lists

Thread: Table transfer

Table transfer

From
Jorge Alberto Fuentes Casillas
Date:
    Good day to all of you.

    Currently i have 2 databases in my postgresql server, and i want to transfer the data from my db1.table1 to my db2.table2.

Both of the tables are very simmilar from each other, so, what i want to do, is to transfer mostly of the rows from one table to the other. I was wondering if you could please help me on how doing this, what i want to know, is if there's something like this:


NSERT INTO db1.table_1 (table1_row1,table1_row2,table1_row3,...,...,...,)

FROM db2.table_2 (table2_row1,table2_row2,table2_row3,...,...,...,)


I hope this haven't been very confusing, I'll appreciate any kind of help that you may provide me. Thousands of thanks in advance.

Greetings.

 

Re: Table transfer

From
Josh Kupershmidt
Date:
On Tue, Sep 21, 2010 at 5:28 PM, Jorge Alberto Fuentes Casillas
<buen_sama@yahoo.com.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.

Re: Table transfer

From
Mladen Gogala
Date:
Jorge Alberto Fuentes Casillas wrote:
>     Good day to all of you.
>
>     Currently i have 2 databases in my postgresql server, and i want
> to transfer the data from my db1.table1 to my db2.table2.
>
> Both of the tables are very simmilar from each other, so, what i want
> to do, is to transfer mostly of the rows from one table to the other.
> I was wondering if you could please help me on how doing this, what i
> want to know, is if there's something like this:
>
>
> NSERT INTO db1.table_1 (table1_row1,table1_row2,table1_row3,...,...,...,)
>
> FROM db2.table_2 (table2_row1,table2_row2,table2_row3,...,...,...,)
>
>
> I hope this haven't been very confusing, I'll appreciate any kind of
> help that you may provide me. Thousands of thanks in advance.
>
> Greetings.
>
>
You will need a script with 2 connections, one  to the source database,
another one to the target database.

--
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Re: Table transfer

From
"Eric Comeau"
Date:
PostgreSQL dblink contrib module may be your friend.

http://www.postgresql.org/docs/8.3/static/dblink.html


-----Original Message-----
From: Mladen Gogala [mailto:mladen.gogala@vmsinfo.com] 
Sent: Wednesday, September 22, 2010 7:40 AM
To: Jorge Alberto Fuentes Casillas
Cc: pgsql-novice@postgresql.org
Subject: Re: Table transfer

Jorge Alberto Fuentes Casillas wrote:
>     Good day to all of you.
>
>     Currently i have 2 databases in my postgresql server, and i want 
> to transfer the data from my db1.table1 to my db2.table2.
>
> Both of the tables are very simmilar from each other, so, what i want 
> to do, is to transfer mostly of the rows from one table to the other. 
> I was wondering if you could please help me on how doing this, what i 
> want to know, is if there's something like this:
>
>
> NSERT INTO db1.table_1 (table1_row1,table1_row2,table1_row3,...,...,...,)
>
> FROM db2.table_2 (table2_row1,table2_row2,table2_row3,...,...,...,)
>
>
> I hope this haven't been very confusing, I'll appreciate any kind of 
> help that you may provide me. Thousands of thanks in advance.
>
> Greetings.
>
>  
You will need a script with 2 connections, one  to the source database, 
another one to the target database.

-- 
Mladen Gogala 
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com

Re: Table transfer

From
"Rob Richardson"
Date:
Using PGAdmin, you can do the following:

1.  Click on the source table.  The SQL to create the table appears.
Copy that code.
2.  Click on your destination database, and then open an SQL window.
Paste the code into that window and execute it.  You now have a table in
your destination database that is identical to the source table.
3.  Right-click on the source table.  Select "Backup", and back up the
table.
4.  Right-click on the destination table.  Select "Restore", and restore
data from the file you created in the previous step.
5.  Write a query that will copy data from the destination table into
the table that actually needs it.
6.  Drop the destination table.

Of course, dblink is a nice tool, and worth knowing.  Someday I should
learn about it.

Robert D. Richardson
Product Engineer Software

RAD-CON, Inc.
TECHNOLOGY: Innovative & Proven
Phone : +1.440.871.5720 ... ext 123
Fax:  +1.440.871.2948
Website:  www.RAD-CON.com
E-mail:  rob.richardson@RAD-CON.com