Refer to another database
How is it possible to refer to another database, like:
select * from otherDatabase.nodes;
I have read something about schemas and I have simply created an own
schema for every database with the same name, but it still does not
work. Is there anything plain and simple?
Best,
Andi
On Tue, Aug 04, 2009 at 04:41:51AM +0200, Andreas Kalsch wrote:
How is it possible to refer to another database, like:
select * from otherDatabase.nodes;
Generally, you use schemas for this. Schemas are just namespaces
inside the one database.
I have read something about schemas and I have simply created an own
schema for every database with the same name, but it still does not
work. Is there anything plain and simple?
SELECT f.one, b.two
FROM
one_schema.foo AS f
JOIN
other_schema.bar AS b
ON (f.id = b.foo_id)
WHERE...
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
Will it decrease performance to refer to other schemas?
David Fetter schrieb:
Show quoted text
On Tue, Aug 04, 2009 at 04:41:51AM +0200, Andreas Kalsch wrote:
How is it possible to refer to another database, like:
select * from otherDatabase.nodes;
Generally, you use schemas for this. Schemas are just namespaces
inside the one database.I have read something about schemas and I have simply created an own
schema for every database with the same name, but it still does not
work. Is there anything plain and simple?SELECT f.one, b.two
FROM
one_schema.foo AS f
JOIN
other_schema.bar AS b
ON (f.id = b.foo_id)
WHERE...Cheers,
David.
Andreas Kalsch <andreaskalsch@gmx.de> writes:
Will it decrease performance to refer to other schemas?
No, not to any noticeable extent. I'm not actually sure whether
parsing would be faster for an explicitly qualified reference or
an unqualified one, but in any case it'd be down in the noise
compared to planning and executing the query.
regards, tom lane
Andreas Kalsch wrote:
Will it decrease performance to refer to other schemas?
no. the schemas are simply two namespaces in the same database.
This is what I want to do: To refer to another database, like I can do
it in MySQL. After adding a schema with database name and refering to it
from another database I get:
ERROR: schema "test" does not exist
Adding the database name:
osm_de=# select * from test.test.newt;
ERROR: cross-database references are not implemented: "test.test.newt"
Could be very simple, if it would be like in MySQL ;) To be completely
in context of a schema - so that I can use all tables without the prefix
- I have to reset the search_path very often. This is probably not very
elegant, but will be my solution then ...
John R Pierce schrieb:
Show quoted text
Andreas Kalsch wrote:
Will it decrease performance to refer to other schemas?
no. the schemas are simply two namespaces in the same database.
Andreas Kalsch <andreaskalsch@gmx.de> writes:
This is what I want to do: To refer to another database, like I can do
it in MySQL.
You're too hung up on the word "database". MySQL "databases" are very
nearly equivalent to Postgres schemas. Use multiple schemas within
one Postgres database and be happy.
- I have to reset the search_path very often. This is probably not very
elegant, but will be my solution then ...
And in MySQL you do "use foo" often. What's the difference?
regards, tom lane
Two causes:
1) I have to rewrite many lines of code = time
2) In MySQL I have access - with superuser rights - to _all_ existing
databases inside the installation. In Postgres I haven't.
But, of course, that are just details.
Best,
Andi
Tom Lane schrieb:
Show quoted text
Andreas Kalsch <andreaskalsch@gmx.de> writes:
This is what I want to do: To refer to another database, like I can do
it in MySQL.You're too hung up on the word "database". MySQL "databases" are very
nearly equivalent to Postgres schemas. Use multiple schemas within
one Postgres database and be happy.- I have to reset the search_path very often. This is probably not very
elegant, but will be my solution then ...And in MySQL you do "use foo" often. What's the difference?
regards, tom lane
Andreas Kalsch wrote:
This is what I want to do: To refer to another database, like I can do
it in MySQL. After adding a schema with database name and refering to
it from another database I get:ERROR: schema "test" does not exist
Adding the database name:
osm_de=# select * from test.test.newt;
ERROR: cross-database references are not implemented: "test.test.newt"Could be very simple, if it would be like in MySQL ;) To be completely
in context of a schema - so that I can use all tables without the
prefix - I have to reset the search_path very often. This is probably
not very elegant, but will be my solution then ...
if it would be "like in mysql", it wouldn't be postgresql.
in postgresql, if you have several namespaces that you want to use
together, use different namespaces in the same database.
now, if you want to 'use all tables without prefix', then why would you
use more than one database or schema or whatever?
the only alternative to access an alternate database within a query is
by using the contributed db_link module, and this is quite
restrictive. since the two databases are completely seperate,
operations like joins are very expensive.
Andreas Kalsch wrote:
Two causes:
1) I have to rewrite many lines of code = time
2) In MySQL I have access - with superuser rights - to _all_ existing
databases inside the installation. In Postgres I haven't.
hmm? the postgresql superuser has full access to all databases in the
cluster. note that, unlike mysql, 'root' is not a postgres user at
all, unless you expressly create a root user and grant it superuser rights.
On 4 Aug 2009, at 7:43, Andreas Kalsch wrote:
1) I have to rewrite many lines of code = time
Why? You do know that you can set multiple schemas in search_path do
you? It's a path ;)
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a78229110131891568549!
In article <4A77C4AF.2060709@gmx.de>,
Andreas Kalsch <andreaskalsch@gmx.de> writes:
To be completely
in context of a schema - so that I can use all tables without the
prefix - I have to reset the search_path very often.
Why? Just say "ALTER DATABASE foo SET search_path = public, bar, baz"
once and you're done.