Thread: How I can join between the other database's tables?
# 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?
hi According to my knowledge this cant yet be achieved in postgresql.. I sincerly hope i am wrong.. Anand 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?
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
hi all, I second this opnion.. Coming from a web development environment, it could help us to distribute load on our servers.. regards Anand On Thu, Dec 28, 2000 at 08:21:26AM -0500, 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. > >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 =============================================
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