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: 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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
