Postgres Pro
Downloads
Home   >   mailing lists

Thread: How to move data from 1 database to another?

How to move data from 1 database to another?

From
"Thomas LeBlanc"
Date:
I have 2 databases. I want to move data from table table1 in database db1 to
table2 in db2.

When I query 1 table from another database, I get a cross-database
references are not implemented.

server1% psql db1
emdata=# select * from db2.public.table1;
ERROR:  Cross-database references are not implemented


What can I do to get the data into another database?

Thanks,
Thomas

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar � get it now!
http://toolbar.msn.com/go/onm00200415ave/direct/01/

Re: How to move data from 1 database to another?

From
"scott.marlowe"
Date:
On Tue, 11 May 2004, Thomas LeBlanc wrote:

> I have 2 databases. I want to move data from table table1 in database db1 to
> table2 in db2.
>
> When I query 1 table from another database, I get a cross-database
> references are not implemented.
>
> server1% psql db1
> emdata=# select * from db2.public.table1;
> ERROR:  Cross-database references are not implemented
>
>
> What can I do to get the data into another database?

You can usually use pg_dump to accomplish such tasks.  It's not something
likely to be implemented any time soon now that schemas have been
implemented.  If you have no great reason to have two seperate databases
versus two schemas, you may want to investigate using schemas in the
future.

For now, you can do something like:

pg_dump -d db1 -t table1 |psql db2

then psql and do

insert into table2 (select * from table1);

Re: How to move data from 1 database to another?

From
"Thomas LeBlanc"
Date:
Actually, the database db2 has all ready been created, plus the table names
are the same, so I can not use pg_dump and restore.

INSERT INTO db1.public.tables SELECT * FROM db2.public.table1

Thanks,
Thomas


>From: "scott.marlowe" <scott.marlowe@ihs.com>
>To: Thomas LeBlanc <thomasatiem@hotmail.com>
>CC: <pgsql-general@postgresql.org>
>Subject: Re: [GENERAL] How to move data from 1 database to another?
>Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
>
>On Tue, 11 May 2004, Thomas LeBlanc wrote:
>
> > I have 2 databases. I want to move data from table table1 in database
>db1 to
> > table2 in db2.
> >
> > When I query 1 table from another database, I get a cross-database
> > references are not implemented.
> >
> > server1% psql db1
> > emdata=# select * from db2.public.table1;
> > ERROR:  Cross-database references are not implemented
> >
> >
> > What can I do to get the data into another database?
>
>You can usually use pg_dump to accomplish such tasks.  It's not something
>likely to be implemented any time soon now that schemas have been
>implemented.  If you have no great reason to have two seperate databases
>versus two schemas, you may want to investigate using schemas in the
>future.
>
>For now, you can do something like:
>
>pg_dump -d db1 -t table1 |psql db2
>
>then psql and do
>
>insert into table2 (select * from table1);
>
>

_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar � get it now!
http://toolbar.msn.com/go/onm00200415ave/direct/01/

Re: How to move data from 1 database to another?

From
Nick Barr
Date:
Thomas LeBlanc wrote:

> Actually, the database db2 has all ready been created, plus the table
> names are the same, so I can not use pg_dump and restore.
>
> INSERT INTO db1.public.tables SELECT * FROM db2.public.table1
>
> Thanks,
> Thomas
>
>
>> From: "scott.marlowe" <scott.marlowe@ihs.com>
>> To: Thomas LeBlanc <thomasatiem@hotmail.com>
>> CC: <pgsql-general@postgresql.org>
>> Subject: Re: [GENERAL] How to move data from 1 database to another?
>> Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
>>
>> On Tue, 11 May 2004, Thomas LeBlanc wrote:
>>
>> > I have 2 databases. I want to move data from table table1 in
>> database db1 to
>> > table2 in db2.
>> >
>> > When I query 1 table from another database, I get a cross-database
>> > references are not implemented.
>> >
>> > server1% psql db1
>> > emdata=# select * from db2.public.table1;
>> > ERROR: Cross-database references are not implemented
>> >
>> >
>> > What can I do to get the data into another database?
>>
>> You can usually use pg_dump to accomplish such tasks. It's not something
>> likely to be implemented any time soon now that schemas have been
>> implemented. If you have no great reason to have two seperate databases
>> versus two schemas, you may want to investigate using schemas in the
>> future.
>>
>> For now, you can do something like:
>>
>> pg_dump -d db1 -t table1 |psql db2
>>
>> then psql and do
>>
>> insert into table2 (select * from table1);
>>
>>
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar – get it now!
> http://toolbar.msn.com/go/onm00200415ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

Yes in fact you can. Use pg_dump to dump the whole database. Then just
do a data only pg_restore (--data-only is the command line switch). Bear
in mind if the restore goes wrong then you are going to have to
dropdb/createdb and then run in the schema again. I hope you have that
schema in a file.

Nick

Re: How to move data from 1 database to another?

From
"scott.marlowe"
Date:
Oh, then just edit the dump to point to the right table and don't
bother with the insert into part.

On Tue, 11 May 2004, Thomas LeBlanc wrote:

> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.
>
> INSERT INTO db1.public.tables SELECT * FROM db2.public.table1
>
> Thanks,
> Thomas
>
>
> >From: "scott.marlowe" <scott.marlowe@ihs.com>
> >To: Thomas LeBlanc <thomasatiem@hotmail.com>
> >CC: <pgsql-general@postgresql.org>
> >Subject: Re: [GENERAL] How to move data from 1 database to another?
> >Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
> >
> >On Tue, 11 May 2004, Thomas LeBlanc wrote:
> >
> > > I have 2 databases. I want to move data from table table1 in database
> >db1 to
> > > table2 in db2.
> > >
> > > When I query 1 table from another database, I get a cross-database
> > > references are not implemented.
> > >
> > > server1% psql db1
> > > emdata=# select * from db2.public.table1;
> > > ERROR:  Cross-database references are not implemented
> > >
> > >
> > > What can I do to get the data into another database?
> >
> >You can usually use pg_dump to accomplish such tasks.  It's not something
> >likely to be implemented any time soon now that schemas have been
> >implemented.  If you have no great reason to have two seperate databases
> >versus two schemas, you may want to investigate using schemas in the
> >future.
> >
> >For now, you can do something like:
> >
> >pg_dump -d db1 -t table1 |psql db2
> >
> >then psql and do
> >
> >insert into table2 (select * from table1);
> >
> >
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar – get it now!
> http://toolbar.msn.com/go/onm00200415ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html
>

Re: How to move data from 1 database to another?

From
Jonathan Bartlett
Date:
> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.

Yes you can, just use pg_dump with the -a flag, and then just use psql
with redirection rather than restore.

Jon

Re: How to move data from 1 database to another?

From
Karsten Hilbert
Date:
> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.
Sure you can:

dropdb db2
createdb db2 ...
> >pg_dump -d db1 -t table1 |psql db2

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

Re: How to move data from 1 database to another?

From
Robert Treat
Date:
dblink is your friend...

rms74=# select dblink_connect('dbname=bms75 port=5474');
 dblink_connect
----------------
 OK
(1 row)
rms74=# create table doesitwork as select * from dblink('select * from
current_downloads'::text) as t1(eid integer,st timestamptz);
SELECT
rms74=# select * from doesitwork
rms74-# ;
  eid  |              st
-------+-------------------------------
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
(3 rows)
rms74=# insert into doesitwork select * from dblink('select * from
current_downloads'::text) as t1(eid integer,st timestamptz);
INSERT 0 3
rms74=# select * from doesitwork;
  eid  |              st
-------+-------------------------------
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
 11892 | 2003-07-14 14:12:56.202592-04
 12590 | 2003-09-11 11:36:22.94156-04
 14998 | 2003-10-20 14:12:58.428304-04
(6 rows)
rms74=#

Robert Treat

On Tue, 2004-05-11 at 12:28, Thomas LeBlanc wrote:
> Actually, the database db2 has all ready been created, plus the table names
> are the same, so I can not use pg_dump and restore.
>
> INSERT INTO db1.public.tables SELECT * FROM db2.public.table1
>
> Thanks,
> Thomas
>
>
> >From: "scott.marlowe" <scott.marlowe@ihs.com>
> >To: Thomas LeBlanc <thomasatiem@hotmail.com>
> >CC: <pgsql-general@postgresql.org>
> >Subject: Re: [GENERAL] How to move data from 1 database to another?
> >Date: Tue, 11 May 2004 10:13:22 -0600 (MDT)
> >
> >On Tue, 11 May 2004, Thomas LeBlanc wrote:
> >
> > > I have 2 databases. I want to move data from table table1 in database
> >db1 to
> > > table2 in db2.
> > >
> > > When I query 1 table from another database, I get a cross-database
> > > references are not implemented.
> > >
> > > server1% psql db1
> > > emdata=# select * from db2.public.table1;
> > > ERROR:  Cross-database references are not implemented
> > >
> > >
> > > What can I do to get the data into another database?
> >
> >You can usually use pg_dump to accomplish such tasks.  It's not something
> >likely to be implemented any time soon now that schemas have been
> >implemented.  If you have no great reason to have two seperate databases
> >versus two schemas, you may want to investigate using schemas in the
> >future.
> >
> >For now, you can do something like:
> >
> >pg_dump -d db1 -t table1 |psql db2
> >
> >then psql and do
> >
> >insert into table2 (select * from table1);
> >
> >
>
> _________________________________________________________________
> FREE pop-up blocking with the new MSN Toolbar – get it now!
> http://toolbar.msn.com/go/onm00200415ave/direct/01/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faqs/FAQ.html

--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL