I am having a problem doing a SELECT across multiple databases... for example I have tables "User" and "Company" in database User, and tables "Sales" and "SalesItems" in database "Transactions".
I want to do a join using tables from both databases, i.e.:
SELECT Users.User.*, Transactions.Sales.date
FROM Users.User, Transactions.Sales
WHERE Users.User.id=Transactions.Sales.userid;
However, PostgreSQL doesn't seem to understand the 'dot' notation to refer to other databases. Is there a different notation that I'm missing, or is this not possible in PostgreSQL?
Sorry, but I think that what you are trying to do is simply impossible, the sql statement is indeed executed on a single db, to whom you are connected.
You can do something similar comparing the results of two queries in php or other language.
This is standard SQL syntax ("Qualified Table Names," I was reminded) and is implemented in MySQL and in Oracle. Is it not supported in Postgres?
I'm not speaking of the sintax, but of a query executed over two dbs at the same time which is illogical.
Before the query you have connected to a db, right?. Then you have sent the query to it, which can work on his tables, but not on tables belonging to the other db.
Anyway ask the man who reminded you to make this work and show the full code here, I'll be more than happy to learn from him.
Regards.
Note that by "databases" here I mean databases within a single instance, on a single Postgres server. Some people might call it a "user" rather than a database name. Given that Postgres allows you to create multiple databases, it's not illogical to select across them.
Here is an example of this usage in the MySQL documentation (http://www.mysql.com/doc/L/e/Legal_names.html).
Alas, I have found my answer: this functionality is on the "TO DO (http://developer.postgresql.org/todo.php)" list. Thanks to Richard Huxton's PostgreSQL Notes (http://www.archonet.com/pgdocs/pgnotes.html) for helping me discover that.
I would like to see this added to the list of issues when converting from MySQL to Postgres. MySQL not only allows this but in fact encourages the use of multiple databases for performance reasons. In my particular case I am writing from scratch for Postgres, but my application naturally wants to use multiple databases and quite a few tables, so I'm going to have to backtrack a bit. Oh well!
Thanks for your full posting, I didn't understand that you were talking of a single instance.
Cheers.
in PostgreSQL databases (in MySQL) are actually schema..
so, within a database, there are multiple schema and multiple user..
differ from MySQL that 1 database is 1 schema.
this concept at first confuse me, but it become clearer as i read this article:
www dot sirfsup dot com/sql_servers/postgresql/pg_schemas.htm