How I can join between the other database's tables?

hi
According to my knowledge this cant yet be achieved in postgresql..
I sincerly hope i am wrong..
Anand

On Tue, Dec 26, 2000 at 09:24:37PM +0530, Anand Raman wrote:

On Wed, Dec 27, 2000 at 12:15:56AM +0900, Ioseph Kim wrote:

# createdb a
# createdb b
# psql a
create table a (name text);
\q
# psql b
create table a (name text);

-------
Now, I want to join table a of database a and table a of database b.
How?

According to my knowledge this cant yet be achieved in postgresql..
I sincerly hope i am wrong..

I'm pretty sure you are right. If your data is related enough to be
joined, it should be related enough to be in the same database.

--
Adam Haberlach |A cat spends her life conflicted between a
adam@newsnipple.com |deep, passionate, and profound desire for
http://www.newsnipple.com |fish and an equally deep, passionate, and
'88 EX500 |profound desire to avoid getting wet.

On Wednesday 27 December 2000 08:44 pm, Adam Haberlach wrote:

I'm pretty sure you are right. If your data is related enough to be
joined, it should be related enough to be in the same database.

I have to disagree. When you start getting into the hundreds of tables, some
form of partitioning is helpful for any number of reasons - security,
backups, data ownership, management, etc. I have seen oracle installations
with hundreds of databases, each with hundreds of tables, and often the users
would write queries that linked across databases....for example linking from
the employee table in the HR database to the log tables in an application
database. If this installation had been "flattened" to one giant database, it
would have been a nightmare.

I for one really wish that PostgreSQL had this functionality. It is one of
the biggest things that I miss from other databases.

Regards,

Adam

--
Adam Rossi
PlatinumSolutions, Inc.
adam.rossi@platinumsolutions.com
http://www.platinumsolutions.com
P.O. Box 31 Oakton, VA 22124
PH: 703.471.9793 FAX: 703.471.7140

Hello all,

I think you are both right. The one says that tables which are related
enough to be joined should stay in one database and the other says that
it could be necessary for many reasons to hold the tables in different
databases. Even if I never was in need to join tables from different
databases (in this point I agree with Adam Haberlach), we have many
applications which use separate databases for lookup-queries or for the
sake of synchronization or replication of databases, therefore I
absolutely agree with Adam Rossi.

In fact, I also would be very content if PostgreSQL would be enabled to
work with different databases (perhaps in some future release?).

Best regards, Jens

Adam Rossi wrote:

On Wednesday 27 December 2000 08:44 pm, Adam Haberlach wrote:

I'm pretty sure you are right. If your data is related enough to be
joined, it should be related enough to be in the same database.

I have to disagree. When you start getting into the hundreds of tables, some
form of partitioning is helpful for any number of reasons - security,
backups, data ownership, management, etc. I have seen oracle installations
with hundreds of databases, each with hundreds of tables, and often the users
would write queries that linked across databases....for example linking from
the employee table in the HR database to the log tables in an application
database. If this installation had been "flattened" to one giant database, it
would have been a nightmare.

I for one really wish that PostgreSQL had this functionality. It is one of
the biggest things that I miss from other databases.
[...]

=============================================
Jens Hartwig
---------------------------------------------
debis Systemhaus GEI mbH
10875 Berlin
Tel. : +49 (0)30 2554-3282
Fax : +49 (0)30 2554-3187
Mobil : +49 (0)170 167-2648
E-Mail : jhartwig@debis.com
=============================================

hi all,
I second this opnion..
Coming from a web development environment, it could help us to
distribute load on our servers..
regards
Anand

Jens Hartwig <jhartwig@debis.com> writes:

In fact, I also would be very content if PostgreSQL would be enabled to
work with different databases (perhaps in some future release?).

I do not think that that's very likely to happen --- at least not in the
foreseeable future. What is likely to happen for 7.2 is support for
schemas as defined by the SQL92 standard. Schemas are basically a level
of naming in between databases and individual tables. By assigning
separate schemas to each user, you could avoid table naming conflicts
between users, but it would still be possible to access a table in
another schema of the same database.

regards, tom lane

On Thursday 28 December 2000 11:12 am, Tom Lane wrote:

Jens Hartwig <jhartwig@debis.com> writes:

In fact, I also would be very content if PostgreSQL would be enabled to
work with different databases (perhaps in some future release?).

I do not think that that's very likely to happen --- at least not in the
foreseeable future. What is likely to happen for 7.2 is support for
schemas as defined by the SQL92 standard. Schemas are basically a level
of naming in between databases and individual tables. By assigning
separate schemas to each user, you could avoid table naming conflicts
between users, but it would still be possible to access a table in
another schema of the same database.

regards, tom lane

Schema support would be a major step forward, and I can't wait to see it
implemented. Thanks for the heads up Tom.

--
Adam Rossi
PlatinumSolutions, Inc.
adam.rossi@platinumsolutions.com
http://www.platinumsolutions.com
P.O. Box 31 Oakton, VA 22124
PH: 703.471.9793 FAX: 703.471.7140