PDA

View Full Version : Help: Copy data from table to table within different DB, How? (PostgreSQL)



stephaniecheah
Jan 5th, 2007, 03:24 AM
Hi,
I'm using PHP with PostgreSQL.

To copy data from one table to another table within the same database can use the following query:
INSERT INTO table2 SELECT * FROM table1 WHERE field1='value1';

My question is:
I have to copy data from one database's table to another database's table, and I was wondering if there is any way to do that than to have a lot of inserts one after another?

Thanks.

guelphdad
Jan 5th, 2007, 05:41 AM
you should probably be able to do that the same way as you do with tables in the same database. your permissions would have to allow you to do that of course.

you would have to refer to your tables by appending the appropriate database name as well:

databasename1.tablenameA
databasename2.tablenameR

for instance.

stephaniecheah
Jan 5th, 2007, 06:28 AM
Looks like it doesn't work. It returned me this error:
ERROR: cross-database references are not implemented: "database1.public.table1"

Can I change any setting to allow cross-database references? Or is there any other suggestion?