Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2009
    Posts
    37

    Unanswered: Query database relationship details

    Hi Guys

    I need to query the catalog to get the details of all of the relationships in a database. So the resulting query will have the following fields:

    FK_TABLE_NAME|FK_COLUMN_NAME|PK_TABLE_NAME|PK_COLU MN_NAME|REL_NAME

    Another query that would be helpful would be to get all the table names in a database excluding the catalog tables.
    If anyone can help with this I would be very thankful, I thought that I would post up here in case someone can easily do it before I spend hours trying :P.

    Thanks
    Liam

  2. #2
    Join Date
    Oct 2009
    Posts
    37
    Ignore this post I did not realise that the information_schema views were SQL standard and supported by most databases as noted here INFORMATION_SCHEMA Support in MySQL, PostgreSQL. This means I can use the same query across different databases.

  3. #3
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Thanks tcliam.

    So, if I understand, there can be no cross-database traffic or joins I should say. OK. If I then choose to store relevant data in separate objects it would be Schemas and not Databases? Schemas hold the table entities?

    Code:
        --  I could join between schemas
        SELECT s1.field, s2.field
        FROM
            Schema_One.Table_x s1 JOIN
            Schema_Two.Table_y s2 ON
            etc, etc, etc

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Yes.

    You could add the same table/view/function/... names in separate schemas without difficulty.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Thanks.

    That seems really, really odd. That you cannot join tables from various DB's. Why would the engineers of an otherwise excellent product hamstring the user that way?

    Guess I have a lot to learn. Need to see some good tutorials - any suggestions? Particularly how to get data from one db to another.
    Last edited by caracadon; 10-28-10 at 10:22.

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Well, pg's schema approach allows you to encapsulate objects (equivalent to separate namespaces in the dotNet progamming environment,) and you can easily join/query between separate schemas. And, by keeping the separate schemas in separate tablespaces, you can physically segregate the underlying physical table files, if desired. This is VERY similar to Oracle's approach.

    There is also a PostgreSQL addon called dbLink that allows you to query remote tables, and to move data between databases, but, it doesn't (as far as I know) allow you to join data from tables in one database to tables in another.
    Last edited by loquin; 10-28-10 at 16:01.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    I have been looking at the DBLink thing - thanks.

  8. #8
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Quote Originally Posted by caracadon View Post
    That seems really, really odd. That you cannot join tables from various DB's. Why would the engineers of an otherwise excellent product hamstring the user that way?
    Postgres schemas behave more like databases in MySQL or SQL Server. Its really quite similar to the way Oracle does it, except that with Oracle you can have only 1 database per instance. So I'm not complaining about Postgres.

  9. #9
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    It really is more of a terminology issue than anything else.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  10. #10
    Join Date
    Oct 2010
    Location
    Western USA
    Posts
    17
    Quote Originally Posted by loquin View Post
    It really is more of a terminology issue than anything else.
    I suppose because it makes absolutely no sense that one database could not access another. Kind of defeats the purpose of having data...

  11. #11
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    If they are related data then put them separate schemas on the same database.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •