Thread: SELECT across multiple db
Hi all, AFAIR this is not possible in standard SQL, but then I found this topic linked from 'PostgreSQL Todo List': http://candle.pha.pa.us/mhonarc/todo.detail/crossdb/msg00000.html It's about 'Remote connections'. (see bottom) Unfortunately I need such a feature now for some of my customers. They have multiple similar Websites (and similar db). Now they want a meta site that takes parts of the different sites ... Of course I could open connections to all databases and handle each db seperately, but in order to GROUP, SORT, UNION etc, it would be much much much more handy do have such a crossdb feature. As the post was from Dec 2001, perhaps it's already implemented ? (Read Acess would be enough, no need for transactions) ---------------------------------- > > The table link will be similar to a view. It could be used like this: > > > > CREATE LINK test as select * from test WITH 'user=postgres host=remote > > db=data'; > > > > SELECT * from test; > > > > or > > > > SELECT * from fubar join test on (fubar.id = test.id) ; ---------------------------------- -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : www.e-trolley.de
Nabil Sayegh wrote: > http://candle.pha.pa.us/mhonarc/todo.detail/crossdb/msg00000.html > It's about 'Remote connections'. (see bottom) See contrib/dblink Joe
Am Fre, 2003-05-16 um 18.48 schrieb Joe Conway: > Nabil Sayegh wrote: > > http://candle.pha.pa.us/mhonarc/todo.detail/crossdb/msg00000.html > > It's about 'Remote connections'. (see bottom) > > See contrib/dblink Thanks for your answer. It's not exactly what I was looking for, because I wanted to do 1 query across many databases and the results put together and sorted etc. But with the keyword 'dblink' and google I found out that what I really wanted is: SCHEMA :) But it seems that this needs 7.3 which is not in debian testing (sarge) because of perl dependencie problems :( It's in unstable (sid) but I can't install sid on a production system. However, thanks all and kudos to the PostgreSQL developers for your great work. -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : www.e-trolley.de
Am Die, 2003-05-20 um 14.38 schrieb Chris Boget: > > It's not exactly what I was looking for, because I wanted to do 1 query > > across many databases and the results put together and sorted etc. > > But with the keyword 'dblink' and google I found out that what I really > > wanted is: SCHEMA :) > > Could you elaborate on the above? How does schema help you with > doing queries across different databases? Not really between multiple databases, but in my case, all the dbs reside on the same host and thatway I can just create 1 db for all of them and put the former dbs as schemas in this 1 db. From there you can REFERENCE, JOIN, etc. across these 'multiple dbs'. If you'd like an example I can mail it to you. If you cant put your dbs together (e.g. different hosts) you probably should look into contrib/dblink as Joe stated. cu -- e-Trolley Sayegh & John, Nabil Sayegh Tel.: 0700 etrolley /// 0700 38765539 Fax.: +49 69 8299381-8 PGP : www.e-trolley.de