Обсуждение: Table transfer
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.
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.
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.
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
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
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