Thread

1. How to move data from 1 database to another?

   Thomas LeBlanc <thomasatiem@hotmail.com> — 2004-05-11T15:54:00Z

   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/
   
   
   

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

   scott.marlowe <scott.marlowe@ihs.com> — 2004-05-11T16:13:22Z

   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);