From jkanter@virginia.edu Thu Jul 8 15:14:21 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BEB5BD1B1B0 for ; Thu, 8 Jul 2004 12:14:21 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 07109-02 for ; Thu, 8 Jul 2004 15:14:15 +0000 (GMT) Received: from unix.mail.Virginia.EDU (unix.mail.Virginia.EDU [128.143.2.28]) by svr1.postgresql.org (Postfix) with ESMTP id 74B44D1B19F for ; Thu, 8 Jul 2004 12:14:12 -0300 (ADT) Received: from smtp.mail.Virginia.EDU (smtp.mail.Virginia.EDU [128.143.22.52]) by unix.mail.Virginia.EDU (Postfix) with ESMTP id A70EEAB67 for ; Thu, 8 Jul 2004 11:14:11 -0400 (EDT) Received: from virginia.edu (d-137-133-128.bootp.Virginia.EDU [137.54.133.128]) by smtp.mail.Virginia.EDU (Postfix) with ESMTP id 467412102C for ; Thu, 8 Jul 2004 11:14:11 -0400 (EDT) Message-ID: <40ED65D1.6050607@virginia.edu> Date: Thu, 08 Jul 2004 11:18:41 -0400 From: Jodi Kanter User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.5) Gecko/20031007 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Postgres Admin List Subject: cross databases? Content-Type: multipart/alternative; boundary="------------070301050507090709040100" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.7 tagged_above=0.0 required=5.0 tests=HTML_70_80, HTML_MESSAGE, HTML_TITLE_EMPTY X-Spam-Level: X-Archive-Number: 200407/93 X-Sequence-Number: 14161 This is a multi-part message in MIME format. --------------070301050507090709040100 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit 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 -- /_______________________________ //Jodi L Kanter BioInformatics Database Administrator University of Virginia (434) 924-2846 jkanter@virginia.edu / / / / / --------------070301050507090709040100 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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
--
________________________________

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

--------------070301050507090709040100-- From devrim@gunduz.org Thu Jul 8 15:34:28 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C8FA1D1B18F for ; Thu, 8 Jul 2004 12:34:28 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 12610-10 for ; Thu, 8 Jul 2004 15:34:20 +0000 (GMT) Received: from emo.org.tr (emo.org.tr [195.142.105.9]) by svr1.postgresql.org (Postfix) with ESMTP id B7649D1B174 for ; Thu, 8 Jul 2004 12:34:18 -0300 (ADT) Received: by emo.org.tr (Postfix, from userid 41643) id EA78A2FAB8; Thu, 8 Jul 2004 18:34:11 +0300 (EEST) Received: from localhost (localhost [127.0.0.1]) by emo.org.tr (Postfix) with ESMTP id E2F3B111EDA; Thu, 8 Jul 2004 18:34:11 +0300 (EEST) Date: Thu, 8 Jul 2004 18:34:09 +0300 (EEST) From: Devrim GUNDUZ X-X-Sender: devrim2@emo.org.tr To: Jodi Kanter Cc: Postgres Admin List Subject: Re: cross databases? In-Reply-To: <40ED65D1.6050607@virginia.edu> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.3 tagged_above=0.0 required=5.0 tests=UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200407/94 X-Sequence-Number: 14162 -----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----- From hforbes@dmr.co.uk Thu Jul 8 15:48:01 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 2C939D1B195 for ; Thu, 8 Jul 2004 12:48:01 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 26202-01 for ; Thu, 8 Jul 2004 15:47:49 +0000 (GMT) Received: from lorraine.ipl.co.uk (lorraine.ipl.co.uk [195.112.52.123]) by svr1.postgresql.org (Postfix) with ESMTP id 561DDD1B181 for ; Thu, 8 Jul 2004 12:47:47 -0300 (ADT) Received: from HFPORT.dmr.co.uk ([195.112.52.21]) by lorraine.ipl.co.uk (8.12.9/8.12.9) with ESMTP id i68FlOMT024864; Thu, 8 Jul 2004 16:47:26 +0100 Message-Id: <5.1.0.14.0.20040708164236.04338eb8@mailserver.dmr.co.uk> X-Sender: hf1@mailserver.dmr.co.uk X-Mailer: QUALCOMM Windows Eudora Version 5.1 Date: Thu, 08 Jul 2004 16:46:27 +0100 To: Jodi Kanter , Postgres Admin List From: Hilary Forbes Subject: Re: cross databases? In-Reply-To: <40ED65D1.6050607@virginia.edu> Mime-Version: 1.0 Content-Type: text/html; charset="us-ascii" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.6 tagged_above=0.0 required=5.0 tests=HTML_20_30, HTML_MESSAGE, MIME_HTML_ONLY X-Spam-Level: X-Archive-Number: 200407/95 X-Sequence-Number: 14163 I have often wanted to do this.  Years ago, when I was working with Sybase databases it was perfectly possible.  You simply qualified the table name with a database identifier as I recall eg

SELECT m.myfield from second_db.mytable m;

The advantages are that you may have two otherwise unconnected systems which need separately backing up etc but where one uses the other for lookup purposes (eg a reservations system uses a lookup to an accounts system to get the customer name and number). 

I have never understood why postgreSQL hasn't seen this as a priority in development in more recent months.  Maybe someone will enlighten us both!

Hilary

At 11:18 08/07/2004 -0400, 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?
Any comments on whether or not that is a good thing?
Thanks
Jodi Kanter
--

_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu


 

 

 

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************
From miker@purplefrog.com Thu Jul 8 16:28:40 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 4F98CD1B1B9 for ; Thu, 8 Jul 2004 13:28:40 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50434-05 for ; Thu, 8 Jul 2004 16:28:39 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id CE922D1B23F for ; Thu, 8 Jul 2004 13:28:38 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i68GSci7039803 for ; Thu, 8 Jul 2004 16:28:38 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i68GBv6q036909 for pgsql-admin@postgresql.org; Thu, 8 Jul 2004 16:11:57 GMT From: Mike Rylander X-Newsgroups: comp.databases.postgresql.admin Subject: Re: cross databases? Date: Thu, 08 Jul 2004 11:55:31 -0400 Organization: Hub.Org Networking Services Lines: 23 Message-ID: References: <40ED65D1.6050607@virginia.edu> Reply-To: miker@purplefrog.com Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7Bit X-Complaints-To: usenet@news.hub.org To: Jodi Kanter User-Agent: KNode/0.7.6 To: pgsql-admin@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/109 X-Sequence-Number: 14177 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 From olly@lfix.co.uk Thu Jul 8 16:01:09 2004 X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CF983D1B2D9 for ; Thu, 8 Jul 2004 13:01:09 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 30919-09 for ; Thu, 8 Jul 2004 16:01:05 +0000 (GMT) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by svr1.postgresql.org (Postfix) with ESMTP id 37B1CD1B211 for ; Thu, 8 Jul 2004 13:00:59 -0300 (ADT) Received: from mailgate.bray-healthcare.com ([80.177.250.202] helo=solport.bray-healthcare.com) by anchor-post-32.mail.demon.net with esmtp (Exim 3.35 #1) id 1BibKT-0003JT-0W; Thu, 08 Jul 2004 17:00:57 +0100 Received: from [192.168.1.18] (helo=braydb.bray-healthcare.com) by solport.bray-healthcare.com with esmtp (Exim 3.36 #1 (Debian)) id 1BibKQ-0000ba-00; Thu, 08 Jul 2004 17:00:54 +0100 Subject: Re: cross databases? From: Oliver Elphick Reply-To: olly@lfix.co.uk To: Devrim GUNDUZ Cc: Jodi Kanter , Postgres Admin List In-Reply-To: References: Content-Type: text/plain Message-Id: <1089302449.29038.10.camel@braydb> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 Date: Thu, 08 Jul 2004 17:00:49 +0100 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200407/96 X-Sequence-Number: 14164 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