Thread: Cross database reference (databases are on the same database cluster)

Hi, group,

I didn't find a syntax for:

select * from database1.tablename; -- while I am login in database2.

Note: database1 and database2 are on one server (one database cluster).

Thank you!

On Tue, 8 Apr 2003, Flower Sun wrote:

>
> Hi, group,
>
> I didn't find a syntax for:
>
> select * from database1.tablename; -- while I am login in database2.
>
> Note: database1 and database2 are on one server (one database cluster).

There are two ways to do what you want.  One is the use dblink in the
contrib directory, which will let you basically do this.

the other is to move your two databases into one database with seperate
schemas, which is the preferred method and will usually result in much
faster queries.

Note that schema support is relatively new (came out in 7.3) so you might
have to update your postgrsql server to use them, but they are well worth
the effort of upgrading to have.

Hi, Scott,

Thank you for your reply. Can you give us a sample of using the first method using dblink?

(My postgresql server is 7.2 preinstalled while installing redhat 8.0, I even cannot find the */contrib/* directory you mentioned).

 "scott.marlowe" <scott.marlowe@ihs.com> wrote:

On Tue, 8 Apr 2003, Flower Sun wrote:

>
> Hi, group,
>
> I didn't find a syntax for:
>
> select * from database1.tablename; -- while I am login in database2.
>
> Note: database1 and database2 are on one server (one database cluster).

There are two ways to do what you want. One is the use dblink in the
contrib directory, which will let you basically do this.

the other is to move your two databases into one database with seperate
schemas, which is the preferred method and will usually result in much
faster queries.

Note that schema support is relatively new (came out in 7.3) so you might
have to update your postgrsql server to use them, but they are well worth
the effort of upgrading to have.

On Tue, 8 Apr 2003, Flower Sun wrote:

>
> Hi, Scott,
> Thank you for your reply. Can you give us a sample of using the first method using dblink?
> (My postgresql server is 7.2 preinstalled while installing redhat 8.0, I even cannot find the */contrib/* directory
youmentioned). 
>  "scott.marlowe" <scott.marlowe@ihs.com> wrote:On Tue, 8 Apr 2003, Flower Sun wrote:

You may or may not have the contrib directory.  While I use redhat, I've
always run Postgresql from a tar.gz build, so I've always had the contrib
directory laying around.  I think you could probably just download 7.2.4
and use the contrib directory from there for dblink.  It has lots of
examples in it on how to use it in the /contrib/dblink/doc directory.

For example:

select dblink_connect('dbname=template1');
 dblink_connect
----------------
 OK
(1 row)

test=# select * from dblink('dbname=template1','select proname, prosrc
from pg_proc')
 as t1(proname name, prosrc text) where proname like 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

so, it's pretty much a function set that connects to the other database
and lets you pull in data from it.

scott.marlowe wrote:
> On Tue, 8 Apr 2003, Flower Sun wrote:
>> Thank you for your reply. Can you give us a sample of using the
>> first method using dblink? (My postgresql server is 7.2
>> preinstalled while installing redhat 8.0, I even cannot find the
>> */contrib/* directory you mentioned).

on my RH 7.3 server it is /usr/lib/pgsql/contrib

> For example:
>
> select dblink_connect('dbname=template1'); dblink_connect

But please note that the syntax Scott shows is from PostgreSQL 7.3 and
greater. Postgres 7.2.x is old, and the version of dblink in it is quite
limited and awkward to use -- I highly recommend updating to PostgreSQL
7.3.2.

Joe

But keeping two databases, allows you to have to physical space (data
directory) ...
Sort of a replication benefit...

scott.marlowe wrote:

>On Tue, 8 Apr 2003, Flower Sun wrote:
>
>
>
>>Hi, group,
>>
>>I didn't find a syntax for:
>>
>>select * from database1.tablename; -- while I am login in database2.
>>
>>Note: database1 and database2 are on one server (one database cluster).
>>
>>
>
>There are two ways to do what you want.  One is the use dblink in the
>contrib directory, which will let you basically do this.
>
>the other is to move your two databases into one database with seperate
>schemas, which is the preferred method and will usually result in much
>faster queries.
>
>Note that schema support is relatively new (came out in 7.3) so you might
>have to update your postgrsql server to use them, but they are well worth
>the effort of upgrading to have.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>
>

On Tue, 8 Apr 2003, Joe Conway wrote:

> scott.marlowe wrote:
> >
> > For example:
> >
> > select dblink_connect('dbname=template1'); dblink_connect
>
> But please note that the syntax Scott shows is from PostgreSQL 7.3 and
> greater. Postgres 7.2.x is old, and the version of dblink in it is quite
> limited and awkward to use -- I highly recommend updating to PostgreSQL
> 7.3.2.

I second that.  Of all the different packages I've ever used, apache and
postgresql are usually the most compelling of all to upgrade.  The new
release versions are always more secure, more stable, faster, and easier
to use.  I.e. the upgrade always saves me enough time down the line that
it's best to do it as soon as the new version can be tested and installed.

I can't say that about all the packages I have to keep happy on my box.
:-)