Thread: Diferent databases on same query...
Hi, I'd like to do a query where can be possible I access tables from diferent databases on the same query. On MySQL, I do: SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM Database1.People1, Database2.Result1 WHERE ... I think on ORACLE works like as: SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM People1@Database1, Result1@Database2 WHERE ... But Who I can to do this on Postgresql ? Thank you. Douglas Rafael.
"Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag news:3BCF859C.D42C8895@inducola.com.br... > Hi, > > I'd like to do a query where can be possible I access tables from > diferent databases on the same query. > On MySQL, I do: > > SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM > Database1.People1, Database2.Result1 WHERE ... > > I think on ORACLE works like as: > > SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM > People1@Database1, Result1@Database2 WHERE ... > > But Who I can to do this on Postgresql ? > You CANNOT do that with PostgreSQL. But why do you want to do that? IMHO it's a rather bas design to hold data in different places, if you need to select them in one query. Is there a real reason to hold the tables in different databases? Andre
Andre, The only reason to have data for one query in different databases is if the databases are on different machines. If you're running on PC-type hardware, you might have to do that some times for performance reasons. But if at that point, you should ask yourself if you shouldn't invest in some faster hardware ;-). Best regards (Tschüs), Chris On Friday, October 19, 2001, at 02:05 , Andre Schnabel wrote: > > "Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag > news:3BCF859C.D42C8895@inducola.com.br... >> Hi, >> >> I'd like to do a query where can be possible I access tables from >> diferent databases on the same query. >> On MySQL, I do: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> Database1.People1, Database2.Result1 WHERE ... >> >> I think on ORACLE works like as: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> People1@Database1, Result1@Database2 WHERE ... >> >> But Who I can to do this on Postgresql ? >> > > You CANNOT do that with PostgreSQL. > But why do you want to do that? IMHO it's a rather bas design to hold data > in different places, if you need to select them in one query. > Is there a real reason to hold the tables in different databases? > > Andre > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -- Chris Ruprecht Network grunt and bit pusher extraordinaíre
>> Hi, >> >> I'd like to do a query where can be possible I access tables from >> diferent databases on the same query. >> On MySQL, I do: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> Database1.People1, Database2.Result1 WHERE ... >> >> I think on ORACLE works like as: >> >> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM >> People1@Database1, Result1@Database2 WHERE ... >> >> But Who I can to do this on Postgresql ? >> > >You CANNOT do that with PostgreSQL. >But why do you want to do that? IMHO it's a rather bas design to hold data >in different places, if you need to select them in one query. >Is there a real reason to hold the tables in different databases? > >Andre Hi, Andre ! Maybe yes, maybe not. I have 6 companies of the same group, little but they are like as, and they share the main database. But there are data that are specific of each one. The design of tables is the same for all them. So, on mysql I had on diferent databases. Thus, on the same query, I select the name of employee, the truck and invoice of the specific company. Two companies have server separated. How can I do to do distribute database server with postgres ? You have a suggestion ? Thank you, Douglas.
Just my two cents on the issue: A good reason would be having several databases which each use the same set of tables which contain some information which takes a lot of storage, such as dictionaries or map information. You wouldn't want to maintain several copies of 500 Mb tables, especially if they get updated frequently. The workaround, for now, is to have a table just for the foreign keys for the different systems, and to query the shared database separately from within your programming environment. Troy > > > "Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag > news:3BCF859C.D42C8895@inducola.com.br... > > Hi, > > > > I'd like to do a query where can be possible I access tables from > > diferent databases on the same query. > > On MySQL, I do: > > > > But Who I can to do this on Postgresql ? > > You CANNOT do that with PostgreSQL. > But why do you want to do that? IMHO it's a rather bas design to hold data > in different places, if you need to select them in one query. > Is there a real reason to hold the tables in different databases? > > Andre > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
"Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag news:3BD2129F.7D200D01@inducola.com.br... > > Hi, Andre ! > > Maybe yes, maybe not. I have 6 companies of the same group, little but > they are like as, and they share the main database. But there are data > that are specific of each one. The design of tables is the same for all > them. So, on mysql I had on diferent databases. Thus, on the same query, > I select the name of employee, the truck and invoice of the specific > company. Two companies have server separated. How can I do to do > distribute database server with postgres ? You have a suggestion ? Hi Douglas, ok, that's a good point to have different databases in one query. Unfortunately there is no way to do this with postgresql. At least no way with the standard distribution. Maybe it's worth to try replication. You should be able to replicate the tables of your main db between the others. But I never tried that. AFAIK, he most advanced replication tooll is PostgreSQL Replicator (http://pgreplicator.sourceforge.net/). Maybe it's the better way to go back(?) to MySQL or have a look at Interbase. Andre
On Saturday 20 October 2001 08:53, you wrote: > Andre, > > The only reason to have data for one query in different databases is if > the databases are on different machines. If you're running on PC-type > hardware, you might have to do that some times for performance reasons. > But if at that point, you should ask yourself if you shouldn't invest in > some faster hardware ;-). There are many more good reasons to do so. Example from my own domain, medicine: 1.) one database hold demographic information. This information is needed by administration, nursing, and medical staff as well as by external service providers such as pathology. 2.) another database holds medical information. The information here is highly sensitive. The database admin should be a medical doctor,and it must be unaccessible to clerical staff. SQL permissions are only a *very* weak protection, not good enough for this purpose. For many good reasons, foremost security, this information will not reside in the same database (sometimes not even on the same machine) as the administrative data or the demographic data. 3.) For obvious reasons, it would be bad to replicate the demographic data in order to provide this data to administration, medical staff etc for their own purposes. Thus, we have multiple databases on multiple servers all depending on each other. Some servers are read-only, others need the backend to log every single query for audit trailing. The latter alone would blow the storage needs out of proportion if applied to the whole set of databases. At present, it is up to our client software to sort this out "manually". A minor headache. Horst
Horst, > There are many more good reasons to do so. Example from my own > domain, > medicine: > Thus, we have multiple databases on multiple servers all depending on > each > other. Your reasons all make sense. What you need is not inter-database queries but a robust middleware layer, such as J2EE. Your sort of situation is why middleware exists. If GreatBridge was still around, I'd reccommend you contact them, as I knwo they implemented a solution for your sort of situation. However, I don't know who'd do it now. -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
On Fri, 26 Oct 2001, Josh Berkus wrote:
> Horst,
>
> > There are many more good reasons to do so. Example from my own
> > domain,
> > medicine:
> > Thus, we have multiple databases on multiple servers all depending on
> > each
> > other.
>
> Your reasons all make sense. What you need is not inter-database
> queries but a robust middleware layer, such as J2EE. Your sort of
> situation is why middleware exists.
Josh,
I'm pretty sure Horst does this already - check out GNU Med.
(Hallo Horst, Viele Gr�sse aus New York. ;-)
I guess the problem is that the procedural language mentioned in
the original post has a few hurdles yet to manage.
BTW, I do this (within the context of very small scripts) using
perl DBI. It is cake with Perl. And - your code is relatively
portable. Aside from certain Oracle idiosyncracies of course
(NVL, DECODE, SYSDATE...)
> If GreatBridge was still around, I'd reccommend you contact them, as I
> knwo they implemented a solution for your sort of situation. However, I
> don't know who'd do it now.
What about RedHat? Are they working on this?
Dieter (Simader) of SQL Ledger also does this sort of thing within
his product (which uses perl dbi): www.sql-ledger.org
Cheers
-------------------------------------------------------------------- Saint Vincent Catholic Medical Centers
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-818-5528
Behavioral Health Services Fax: 718-818-5056
Residential Services Mobile: 917-282-7359
--------------------------------------------------------------------
/* Rekordmeister ist nur der FC Bayern M�nchen! */
--------------------------------------------------------------------