Thread: how to refer to tables in another database( or schema as oracle refers to)

Hello,
I'm still new here and new to PostgreSQL, I'd like to know that after I 
have created two databases on my site, such as one is called backend, and 
another one is called admin, how do I refer(query) the table from backend 
while I'm connected to admin database, or is it possible to do that in 
PostgreSQL? any answer is appreciated.

JJW.
8/19/2002

Hi Jiaqing,

Basically - you can't.  There is a program in the contrib/dblink directory
that can help you though.

Regards,

Chris

> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Jiaqing
> Sent: Tuesday, 20 August 2002 5:53 AM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] how to refer to tables in another database( or schema as
> oracle refers to)
>
>
> Hello,
> I'm still new here and new to PostgreSQL, I'd like to know that after I
> have created two databases on my site, such as one is called backend, and
> another one is called admin, how do I refer(query) the table from backend
> while I'm connected to admin database, or is it possible to do that in
> PostgreSQL? any answer is appreciated.
>
> JJW.
> 8/19/2002
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

> Basically - you can't.  There is a program in the contrib/dblink directory
> that can help you though.
>

My take on this dblink program is to create SQL as a view.
For example coming from Oracle you might do "SELECT one, two, FROM foo.bar".
Convert that to a VIEW called simply "bar" or "foo_bar" that contains your
dblink query and there will be minimal chnages you'll have to make to your
actual code.
This could also be used to get around synonyms as well.

Cheers,Graeme

On Mon, 19 Aug 2002, Jiaqing wrote:

> Hello,
> I'm still new here and new to PostgreSQL, I'd like to know that after I
> have created two databases on my site, such as one is called backend, and
> another one is called admin, how do I refer(query) the table from backend
> while I'm connected to admin database, or is it possible to do that in
> PostgreSQL? any answer is appreciated.

In addition to previous answers (dblink related), in 7.3 schemas will
be implemented and you may be able to use one database with two schemas
in which case normal sql should work.  This isn't out yet, so it's a
future concern.

On Mon, 19 Aug 2002, Stephan Szabo wrote:

> On Mon, 19 Aug 2002, Jiaqing wrote:
>
> > Hello,
> > I'm still new here and new to PostgreSQL, I'd like to know that after I
> > have created two databases on my site, such as one is called backend, and
> > another one is called admin, how do I refer(query) the table from backend
> > while I'm connected to admin database, or is it possible to do that in
> > PostgreSQL? any answer is appreciated.
>
> In addition to previous answers (dblink related), in 7.3 schemas will
> be implemented and you may be able to use one database with two schemas
> in which case normal sql should work.  This isn't out yet, so it's a
> future concern.

One problem space that I think either of these solutions might address
is the issue of having static "country code" and "state code" tables
reproduced on many databases throughout an installation. Would anyone
recommend either of these solutions, or another one, for addressing this
issue?

I'm not looking forward to the day when a new country appears,
and I have to find all the places I have country code lists to add it.
:)
 -mark

http://mark.stosberg.com/