Обсуждение: SELECT from a table in another database
Hi! I'm having a problem with a SQL-sentence like this: SELECT * from DATABASE.Table; I get an error saying: 'parse error at or near "."' I've done this in MySQL and Oracle without any problem, and found it strange that I couldn't do it in Postgres. Am I missing something, or do I have to use another syntax to be able to select from tables in another databases?
Trygve Falch wrote: > > Hi! > > I'm having a problem with a SQL-sentence like this: > > SELECT * from DATABASE.Table; > > I get an error saying: 'parse error at or near "."' > > I've done this in MySQL and Oracle without any problem, and found it > strange that I couldn't do it in Postgres. > > Am I missing something, or do > I have to use another syntax to be able to select from tables in another > databases? AFAIK cross database joins are not possible in PostgreSQL. Regards, Nils Zonneveld
Trygve Falch wrote: > > "Nils Zonneveld" <nils@mbit.nl> wrote in message > news:3B015964.AF072405@mbit.nl... > > > AFAIK cross database joins are not possible in PostgreSQL. > > Hi! Thanks for the answer. > > Actually I think I found 'Allow queries across multiple databases' in the > TODO-list under something they call 'Exotic feature'. I thought that this > feature was relativly basic and standard-feature in most DB's. > > Maybe I am exotic to need it. *sob* I don't know what you are using those database for, but nothing prevents you from letting your clients connect to the different databases the same time. I use for instance a MS Access front end (yes, I know but clients request etc.) to connect to different ODDBC sources and t works just fine. Another solution is of course to integrate the tables that you need in your joins in one database. HTH, Nils
"Nils Zonneveld" <nils@mbit.nl> wrote in message news:3B015964.AF072405@mbit.nl... > AFAIK cross database joins are not possible in PostgreSQL. Hi! Thanks for the answer. Actually I think I found 'Allow queries across multiple databases' in the TODO-list under something they call 'Exotic feature'. I thought that this feature was relativly basic and standard-feature in most DB's. Maybe I am exotic to need it. *sob*
>I'm having a problem with a SQL-sentence like this: > > SELECT * from DATABASE.Table; > >I get an error saying: 'parse error at or near "."' I don't believe this is possible with Postgres (nor is it desirable (IMHO)). len morgan
Queries across multiple databases (was: SELECT from a table in another database).
От
"Trygve Falch"
Дата:
In article <3B01ACE4.2A47F2C6@mbit.nl>, "Nils Zonneveld" <nils@mbit.nl> wrote: >> Actually I think I found 'Allow queries across multiple databases' in >> the TODO-list under something they call 'Exotic feature'. I thought >> that this feature was relativly basic and standard-feature in most >> DB's. > I don't know what you are using those database for, but nothing prevents > you from letting your clients connect to the different databases the > same time. But that requires me to make a new database connection for each database I need to access. And putting 200+ tables in one single database is not an option. The application which needs to be able to do this is a cross-database-application (MSSQL, Oracle, Sybase) and I have almost no room for doing major changes to the SQL which this application uses. But the lack of this feature in Postgres makes it almost impossible to make a structured database design for huge application. I know this question have been asked before in another postgres forum as early as 1998, and what Bruce Momjian said then was that most commercial databases couldn't do it, which was probably right for 1998, but today even MySQL can do this! Sybase, Oracle and MSSQL can also do this. I think even DB2 and Informix can. I was really suprised when I discovered that this was even an issue with Postgres, because everything else in this wonderful DBM is on an enterprise level of quality and functionality. Sadly, this means I'll have to stick to Oracle (even if I really didn't want to) until this issue is resolved in Postgres. (crossposted to comp.databases.postgresql.hackers).
Re: Queries across multiple databases (was: SELECT from a table in another database).
От
Nils Zonneveld
Дата:
Trygve Falch wrote: > > In article <3B01ACE4.2A47F2C6@mbit.nl>, "Nils Zonneveld" <nils@mbit.nl> > wrote: > > >> Actually I think I found 'Allow queries across multiple databases' in > >> the TODO-list under something they call 'Exotic feature'. I thought > >> that this feature was relativly basic and standard-feature in most > >> DB's. > > > I don't know what you are using those database for, but nothing prevents > > you from letting your clients connect to the different databases the > > same time. > > But that requires me to make a new database connection for each database I > need to access. > > And putting 200+ tables in one single database is not an option. > > The application which needs to be able to do this is a > cross-database-application (MSSQL, Oracle, Sybase) and I have almost no > room for doing major changes to the SQL which this application uses. > If you have a cross-database-application you must already have multiple connections to several database-engines at the same time. Or is that a situation you want to get rid of? > But the lack of this feature in Postgres makes it almost impossible to > make a structured database design for huge application. I know this > question have been asked before in another postgres forum as early as > 1998, and what Bruce Momjian said then was that most commercial databases > couldn't do it, which was probably right for 1998, but today even MySQL > can do this! Sybase, Oracle and MSSQL can also do this. I think even DB2 > and Informix can. > > I was really suprised when I discovered that this was even an issue with > Postgres, because everything else in this wonderful DBM is on an > enterprise level of quality and functionality. > > Sadly, this means I'll have to stick to Oracle (even if I really didn't > want to) until this issue is resolved in Postgres. > I'm not a PostgreSQL developer, just a humble user :-) If you have the money and resources to use Oracle, use Oracle if you really need schema support. If not, there are workarounds. At the moment PostgreSQL doesn't have schema support. But there's light at the end of the tunnel: I've heard Tom Lane mention schema support several times (can you give us an estimate when schema support will be available in PostgreSQL Tom?). Regards, Nils Zonneveld
"Trygve Falch" <trf@ssb.no> writes: > And putting 200+ tables in one single database is not an option. Why not? regards, tom lane