query multiple databases

Adam Fisher wrote:

Hi All,
I am just starting out with postgreSQL. I have retail inventory system
written in MS-SQL, but now I want to use Linux and, hence, postgreSQL.

My existing app uses lots of SQL that query 2 or more seperate databases. I
have just read that this can;t be done in postgreSQL. Is this true?

Yes.

If so, are there any obvious work-arounds? Or must I simply combine all my
databases into one? I really don't want to do that!

I don't know.

If this limitation exists, can anybody recomend another database that
doesn't cost a fortune but has similar strength-of-features to
MS-SQL/postgreSQL?

Arne.

Adam Fisher wrote:

Hi All,
I am just starting out with postgreSQL. I have retail inventory system
written in MS-SQL, but now I want to use Linux and, hence, postgreSQL.

My existing app uses lots of SQL that query 2 or more seperate

databases. I

have just read that this can;t be done in postgreSQL. Is this true?

Are you sure you are not confusing databases and tables? They are not the
same thing although M$ does try and corrupt the meaning of common terms. I
routinely pull data from multiple tables in the SAME database and can see no
reason why a well designed/normalized system requires more than one
database. I use different databases for different customers but each
database contains many tables/indexes/sequences etc.

len morgan

My existing app uses lots of SQL that query 2 or more seperate databases. I
have just read that this can;t be done in postgreSQL. Is this true?

Yes. IIRC, Postgres only validates access on connection. With psql you can
switch between databases, but I don't know if that feature is available
through other means such as PHP or DBI.

If so, are there any obvious work-arounds? Or must I simply combine all my
databases into one? I really don't want to do that!

Your best bet would probably be multiple connections. As you can see, there
would be no way to combine results from different connections/databases
except in the application, and you would not be able to do cross-database
joins, so you would have to alter your application logic

If this limitation exists, can anybody recomend another database that
doesn't cost a fortune but has similar strength-of-features to
MS-SQL/postgreSQL?

Although I am an open-source advocate, I am also a businessman, and although
you could get Postgres to do what you need relatively efficiently, sometimes
the time needed to implement workarounds makes Postgres simply not cost
effective. When I can't fall back on open source, I fall back to the
underdogs with good products. One I can recommend is FrontBase
(http://www.frontbase.com). For about $1k you get 100% SQL92 compliance,
replication, live backup, and no per-seat or any other additional costs.
<DISCLAIMER>I have no relationship with the company and no extensive
production experience with the DB system. I cannot vouch for the stability,
security, or ability to service customers. My recommendation is based on
research and testing only.</DISCLAIMER>

Keary Suska
Esoteritech, Inc.
"Leveraging Open Source for a better Internet"