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