Thread: cross databases?

I am working with someone who uses MySQL and says that he has two separate databases in which he can query across. I am curious if that is something that can be done in postgres?
Any comments on whether or not that is a good thing?
Thanks
Jodi Kanter

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Thu, 8 Jul 2004, Jodi Kanter wrote:

> I am working with someone who uses MySQL and says that he has two
> separate databases in which he can query across. I am curious if that is
> something that can be done in postgres?

Yes, it's possible with PostgreSQL.

See contrib/dblink.

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
            http://www.tdmsoft.com
            http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFA7Wlztl86P3SPfQ4RAnKTAJ41Xlrx8zQueEZhMlmnbof0hNbfJwCghlEf
LPjc9KUai+jU3pgXC+a1AMI=
=JxHt
-----END PGP SIGNATURE-----

I have often wanted to do this.  Years ago, when I was working with Sybase databases it was perfectly possible.  You
simplyqualified the table name with a database identifier as I recall eg <br /><br /> SELECT m.myfield from
second_db.mytablem;<br /><br /> The advantages are that you may have two otherwise unconnected systems which need
separatelybacking up etc but where one uses the other for lookup purposes (eg a reservations system uses a lookup to an
accountssystem to get the customer name and number).  <br /><br /> I have never understood why postgreSQL hasn't seen
thisas a priority in development in more recent months.  Maybe someone will enlighten us both!<br /><br /> Hilary<br
/><br/> At 11:18 08/07/2004 -0400, Jodi Kanter wrote:<br /><br /><blockquote cite="cite" class="cite" type="cite">I am
workingwith someone who uses MySQL and says that he has two separate databases in which he can query across. I am
curiousif that is something that can be done in postgres? <br /> Any comments on whether or not that is a good
thing?<br/> Thanks<br /> Jodi Kanter<br /> -- <br /><br /><i>_______________________________<br /> Jodi L Kanter<br />
BioInformaticsDatabase Administrator<br /> University of Virginia<br /> (434) 924-2846<br /><a
href="mailto:jkanter@virginia.edu">jkanter@virginia.edu</a></i><br/><br />  <br /><br /><i> <br /></i><br
/><i> </i></blockquote><p>Hilary Forbes<br /> The DMR Information and Technology Group  (<a eudora="autourl"
href="http://www.dmr.co.uk/">www.dmr.co.uk</a>)<br/> Direct tel 01689 889950 Fax 01689 860330 <br /> DMR is a UK
registeredtrade mark of DMR Limited<br /> ********************************************************** 

On Thu, 2004-07-08 at 16:34, Devrim GUNDUZ wrote:
> On Thu, 8 Jul 2004, Jodi Kanter wrote:
>
> > I am working with someone who uses MySQL and says that he has two
> > separate databases in which he can query across. I am curious if that is
> > something that can be done in postgres?
>
> Yes, it's possible with PostgreSQL.
>
> See contrib/dblink.

But a more convenient alternative is to use schemas, which MySQL does
not (I think) support.

Oliver Elphick

<posted & mailed>

Jodi Kanter wrote:

> I am working with someone who uses MySQL and says that he has two
> separate databases in which he can query across. I am curious if that is
> something that can be done in postgres?

Postgresql implements SQL standard SCHEMAs which, depending on your exact
needs, may serve the same purpose.  The other option is the
'contrib/dblink' module.

> Any comments on whether or not that is a good thing?

In my opinion, SCHEMAs are a beter way than multiple databases to deal with
multiple logical datasets that you want to query across.


--miker

> Thanks
> Jodi Kanter