Postgres Pro
Downloads
Home   >   mailing lists

Thread: accessing data from other databases?

accessing data from other databases?

From
"Celia McInnis"
Date:
I have two separate (postgres) databases each of which contain a set of
tables with the same names. Now it turns out that one of the tables between
these two databases can be shared. What's my best way of proceeding? I was
hoping that I could just access this table from either database, but when I
try to do so I get the error:

ERROR:   cross-database references are not implemented...

I'd rather not combine the databases, since they are huge, and apart from
this one table which is relevant to both, they are quite independent. I'm
also not keen on maintaining two copies of the one table which is relevant to
both, since it will change with time. Are there any other options?

Thanks for any suggestions,
Celia McInnis

Re: accessing data from other databases?

From
"Sean Davis"
Date:
See the contrib/dblink directory in the postgres distribution.

Sean
----- Original Message -----
From: "Celia McInnis" <celia@drmath.ca>
To: <pgsql-novice@postgresql.org>
Sent: Friday, June 10, 2005 8:57 PM
Subject: [NOVICE] accessing data from other databases?


>I have two separate (postgres) databases each of which contain a set of
> tables with the same names. Now it turns out that one of the tables
> between
> these two databases can be shared. What's my best way of proceeding? I was
> hoping that I could just access this table from either database, but when
> I
> try to do so I get the error:
>
> ERROR:   cross-database references are not implemented...
>
> I'd rather not combine the databases, since they are huge, and apart from
> this one table which is relevant to both, they are quite independent. I'm
> also not keen on maintaining two copies of the one table which is relevant
> to
> both, since it will change with time. Are there any other options?
>
> Thanks for any suggestions,
> Celia McInnis
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>

Re: accessing data from other databases?

From
Bruno Wolff III
Date:
On Fri, Jun 10, 2005 at 19:57:37 -0500,
  Celia McInnis <celia@drmath.ca> wrote:
>
> ERROR:   cross-database references are not implemented...
>
> I'd rather not combine the databases, since they are huge, and apart from
> this one table which is relevant to both, they are quite independent. I'm
> also not keen on maintaining two copies of the one table which is relevant to
> both, since it will change with time. Are there any other options?

contrib/dblink might be of help. It allows cross database references, but
there are probably some drawbacks that may or may not be a problem in
your specific case.