#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    1
    Rep Power
    0

    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.
  2. #2
  3. No Profile Picture
    Apprentice Deity
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 1999
    Location
    Niagara Falls (On the wrong side of the gorge)
    Posts
    3,237
    Rep Power
    19
    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.
  4. #3
  5. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    It's called dblink.
    PostgreSQL, it's what's for dinner...
  6. #4
  7. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  8. #5
  9. Me likey breadsticks...
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jan 2003
    Location
    Los Angeles
    Posts
    1,189
    Rep Power
    18
    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
    PostgreSQL, it's what's for dinner...
  10. #6
  11. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    62
    Originally posted by bcyde
    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).
    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.


    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.?
    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.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    373
    Rep Power
    12
    may there be security issues or data backup issues in having multiple schemas as opposed to seperate instances of a db?

IMN logo majestic logo threadwatch logo seochat tools logo