Join with tables in different db

I have to join fields in two tables that are in two different db.
Is it possible to do that in postgresql, and how can I do that?
Thanks.

Short answer: no.

Long answer: yes, IIRC, there is a plugin available in contrib that allows this. Do a search on the postgres site as I don't remember what it's called off the top of my head.

It's called dblink.

Another thing to remember: if you use PostgreSQL 7.3+, you can use schemas to make one database "seem" like multiple databases, including the ability to query between them.

Really, this is not a cheap hack. Schemas are widely used in Oracle and other high-end systems, and they really are different databases from the user's perspective, so there is no reason not to take advantage of schemas.

I don't mean to thread hijack, but if rycamor doesn't mind I'd like to hear some pros and cons for separate database vs. schemas, and possible cases of misusing/overusing schemas.

For example, I can see placing all intranet apps in one database separated by different schemas for app specific info to centralize authentication and general info (emp info, dept info). Would this be a reasonable idea? If so, when would separating into different databases be more feasible (besides just having totally unrelated data).

Also, how does using schemas have any adverse affects to db maintenance. For example longer vacuums (when doing the whole db as opposed to a specific table), vacuum fulls taking down the whole database, etc.?


-b

Schemas are nothing more than nesting of "subdatabases". As of version 7.3, you are always using a schema, even if it is the default "public" schema. Think of schemas exactly the same way you would think of namespaces in a programming language. For example, PHP5 might have namespace support, which would allow many related classes to all be called under one namespace, rather than pollutin the global namespace. Thus you could have a "Geometry" namespace, with a class called "radius", and you could have a "Geography" namespace, with its own class "radius". Now, with PHP4 you could just fake namespaces by doing something like

geom_radius
geom_chord
geom_circumference

and

geog_radius
geog_circumference

You get the idea. But it is a very inelegant way to solve the problem, and doesn't prevent operations in one area of concern from accessing operations in the other, since they are all available globally. Namespaces usually come with a few other features, such as the ability to prevent a class from being accessed by anything except other classes in its namespace, etc...

So, schemas allow you to do exactly the same kind of separation, and all aspects of a schema are under the control of the user/group permissions system. Thus, depending on who you are when you log into the database, you see different schemas available to you. Each schema is essentially a nested database, with its own complete set of tables, views, etc... BUT (here's the fun part) views, queries, triggers, etc... can span multiple schemas, as long as the user permissions are resolved properly.

A cool example might be if you are providing a remote service where multiple customers each have their own database, but you as the "admin" need the ability to query everyone's "transaction_log" table. Well, then you can define a view in the "admin" schema that joins each of the other tables with a UNION, so they all look like one large table to you.



I doubt this is a serious consideration except for very large databases. Yes, you do lose physical independence of each database, but remember, pg_dump can be run on schemas instead of the whole DB, and VACUUM is schema-aware, so you should be able to do VACUUM FULL myschema.*, or something like that.

may there be security issues or data backup issues in having multiple schemas as opposed to seperate instances of a db?