Re: Link tables from different databases

From: Shane Ambler <pgsql(at)Sheeky(dot)Biz>
To: "Reuben D(dot) Budiardja" <techlist(at)pathfinder(dot)phys(dot)utk(dot)edu>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Link tables from different databases
Date: 2008-05-18 15:52:07
Message-ID: 483050A7.6040802@Sheeky.Biz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Reuben D. Budiardja wrote:
> Hello,
> Suppose I have two tables db1.table1 and db2.table2. Is there anyway I can
> actually have db2.table2 a link to db1.table1 ? What I meant is similar to a
> symlink in Unix filesystem (db2.table2 is a symlink to db1.table1).
>
> I want it so that every operation on db1.table1 (ie. INSERT, UPDATE, DELETE)
> also happens to db2.table2, but similarly I want it that application can do
> those operations on db2.table2 but actually it does it on db1.table1 behind
> the scene.
>
> Is there anything like this in postgresql ? I am using version 7.4 by the way.
>
> Thanks in advance for any help.
> RDB

You will get people saying that this is bad design and they will be
right. You should think about your design before going in this direction.

Does this same data belong to both db's? Can the two db's be merged into
one using different schema to separate the data instead of different db's?

There isn't a simple way (like a simple symlink) but dblink in the
contrib folder may help.

To automate the process you will need to add triggers to pass along all
the changes but I think you will find that being able to update the
table in both db's will cause you some headaches (think endless loops).

You may want to have one db with the table and use functions to access
the data from the other db.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2008-05-18 16:42:20 Re: Postgresql service stops every few minutes on Vista Biz
Previous Message Teodor Sigaev 2008-05-18 15:12:05 Re: tsearch2 on-demand dictionary loading & using functions in tsearch2