Обсуждение: Multiple database queries
Hello, I was using many years ago Sybase that was able then to query several tables that could be located in more than one database... I know that Postgresql did not at the time have this capability. Does the new version 8.0.x ofter this feature? We need to create several databases based on our customer's specs... but we need our own customer, permission, history, etc database. We want to be able to query 2 database that would have share customer's ID and other info. If not, what is the best workaround? Thank's to all. Mark
Cosmopo <tempo@marquo.com> schrieb: > Does the new version 8.0.x ofter this feature? We need to create > several databases based on our customer's specs... but we need our own > customer, permission, history, etc database. We want to be able to > query 2 database that would have share customer's ID and other info. You can't. > If not, what is the best workaround? dblink. http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/dblink Regards, Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
Sybase and Microsoft SQL Server are somewhat silimar on the concept `database'. They both support queries across multiple databases. But pgsql's database is different, I think you can use pgsql's schemas to cope with problem. Regards, William ZHANG "Cosmopo" <tempo@marquo.com> wrote > Hello, > > I was using many years ago Sybase that was able then to query several > tables that could be located in more than one database... I know that > Postgresql did not at the time have this capability. > > Does the new version 8.0.x ofter this feature? We need to create > several databases based on our customer's specs... but we need our own > customer, permission, history, etc database. We want to be able to > query 2 database that would have share customer's ID and other info. > > If not, what is the best workaround? > Thank's to all. > Mark >
On Sat, Oct 01, 2005 at 09:37:49PM -0700, Cosmopo wrote: > Hello, > > I was using many years ago Sybase that was able then to query > several tables that could be located in more than one database... I > know that Postgresql did not at the time have this capability. > > Does the new version 8.0.x ofter this feature? We need to create > several databases based on our customer's specs... but we need our > own customer, permission, history, etc database. We want to be able > to query 2 database that would have share customer's ID and other > info. I don't know exactly what you mean by tables that can be located in more than one database, as "database" has several definitions, but PostgreSQL has "schemas" which are essentially namespaces inside the same database, and you can query across schemas without extra add-ons. You can also use Slony-I master-slave replication <http://slony.info> to replicate, for example, your customer table to several different databases. The replicated tables are read-only on the slaves, but that shouldn't present insurmountable problems. HTH :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
On Sun, Oct 02, 2005 at 11:20:38PM -0400, Marc Andre Paquin wrote: > Le 2005 10 02 14:36, David Fetter a ecrit: > > On Sat, Oct 01, 2005 at 09:37:49PM -0700, Cosmopo wrote: > > > Hello, > > > > > > I was using many years ago Sybase that was able then to query > > > several tables that could be located in more than one > > > database... I know that Postgresql did not at the time have this > > > capability. > > > > > > Does the new version 8.0.x ofter this feature? We need to create > > > several databases based on our customer's specs... but we need > > > our own customer, permission, history, etc database. We want to > > > be able to query 2 database that would have share customer's ID > > > and other info. > > > > I don't know exactly what you mean by tables that can be located > > in more than one database, as "database" has several definitions, > > but PostgreSQL has "schemas" which are essentially namespaces > > inside the same database, and you can query across schemas without > > extra add-ons. > > Hello, thank you... > > Well, when I do a "createdb owndb1" and "createdb customerdb2" I > want to put a table "customer" in the owndb1 with user name, account > specification that will be valuable for all our customers... In PostgreSQL, you'd use schemas for this kind of separation. Each can have its own owner, it's own constellation of permissions, and any or none of it can overlap with those of another schema. http://www.postgresql.org/docs/current/static/sql-createschema.html http://www.postgresql.org/docs/current/static/ddl-schemas.html Also handy, and from the SQL:2003 standard: http://www.postgresql.org/docs/current/static/information-schema.html > I would like to make a query like this: > > select > owndb1.customer.co_name,owndb1.customer.passwd,customerdb2.event.title,... > from owndb1.customer,customerdb2.event where customerdb2.info.user_id = > owndb1.customer.user_id; > > In Sybase I used to be able to query accross 2 databases since we > usualy made a database per customer but account, permissions info, > etc for those customers where in a central database. > > From your response, I guess this is not possible even many years > later. Not just possible, but easy :) Cheers, D -- David Fetter david@fetter.org http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote!
Le 2005 10 02 14:36, David Fetter a ecrit: > On Sat, Oct 01, 2005 at 09:37:49PM -0700, Cosmopo wrote: > > Hello, > > > > I was using many years ago Sybase that was able then to query > > several tables that could be located in more than one database... I > > know that Postgresql did not at the time have this capability. > > > > Does the new version 8.0.x ofter this feature? We need to create > > several databases based on our customer's specs... but we need our > > own customer, permission, history, etc database. We want to be able > > to query 2 database that would have share customer's ID and other > > info. > > I don't know exactly what you mean by tables that can be located in > more than one database, as "database" has several definitions, but > PostgreSQL has "schemas" which are essentially namespaces inside the > same database, and you can query across schemas without extra add-ons. Hello, thank you... Well, when I do a "createdb owndb1" and "createdb customerdb2" I want to put a table "customer" in the owndb1 with user name, account specification that will be valuable for all our customers... I would like to make a query like this: select owndb1.customer.co_name,owndb1.customer.passwd,customerdb2.event.title,... from owndb1.customer,customerdb2.event where customerdb2.info.user_id = owndb1.customer.user_id; In Sybase I used to be able to query accross 2 databases since we usualy made a database per customer but account, permissions info, etc for those customers where in a central database. From your response, I guess this is not possible even many years later. Some data like cities, regions, countries can be made once and put in a "general purpose database"... that another database (entire independant data structure) can reuse some basic info on geographic for example... If USA is county_id 27 in one database, why not simply put a field like country_id in a table of another database and use this info with a simple query... Thanks! -- Mark ~~~~