Thread: 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

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).

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