Обсуждение: Multidatabase query
Hi all, is possible in PostgreSQL to create query between multidatabase like Sql Server 2005? An example: SELECT db1.a.id FROM db1.a UNION db2.b.id FROM db2.b Where "db1" is a database and "db2" is another database. "a" is a table in database "db1" and "b" is a table in database"db2" Best regards, Mauro
Hi Mauro,
Not possible in PostgreSQL.
However, you can use dblink for creating query between multidatabase as given below:
select empno from dblink('dbname=edb','SELECT empno from emp') as emp(empno numeric)
union
select empno from dblink('dbname=enterprisedb','SELECT empno from emp') as emp(empno numeric);
where edb and enterprisedb are database names
For More information about dblink, please follow the link given below:
http://www.postgresql.org/docs/current/static/dblink.html
http://www.enterprisedb.com/docs/en/8.3/oracompat/EnterpriseDB_OraCompat_EN_8.3-49.htm
Regards,
Vibhor Kumar
www.enterprisedb.com
Mauro Bertoli wrote:
Not possible in PostgreSQL.
However, you can use dblink for creating query between multidatabase as given below:
select empno from dblink('dbname=edb','SELECT empno from emp') as emp(empno numeric)
union
select empno from dblink('dbname=enterprisedb','SELECT empno from emp') as emp(empno numeric);
where edb and enterprisedb are database names
For More information about dblink, please follow the link given below:
http://www.postgresql.org/docs/current/static/dblink.html
http://www.enterprisedb.com/docs/en/8.3/oracompat/EnterpriseDB_OraCompat_EN_8.3-49.htm
Regards,
Vibhor Kumar
www.enterprisedb.com
Mauro Bertoli wrote:
Hi all, is possible in PostgreSQL to create query between multidatabase like Sql Server 2005? An example: SELECT db1.a.id FROM db1.a UNION db2.b.id FROM db2.b Where "db1" is a database and "db2" is another database. "a" is a table in database "db1" and "b" is a table in database "db2" Best regards, Mauro
On Fri, 27 Mar 2009, Mauro Bertoli wrote: > SELECT > db1.a.id FROM db1.a > UNION > db2.b.id FROM db2.b > > Where "db1" is a database and "db2" is another database. "a" is a > table in database "db1" and "b" is a table in database "db2" You might be able to create the equivalent of a union by having a front-end program connect to both databases. You can have two open connections and query them in turn and put the union together. Particularly if it is just a union of keys. But the essential idea is that the database is the universe of data. E.g. you want to minimize data redundancy in a db, but you wouldn't want to do that across databases, because they are different, independent worlds. What is the business of one is not the business of the other. A query like the above seems to defeat this idea. What you are calling databases, or what the other DBMS calls databases, arguably are not. -- Elle