Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0

    PostgreSQL Data Dictionary?


    Good friends,

    Am getting into PostgreSQL and would like to ask if it has a data-dictionary, and if so, where can one read about it?

    Thanks already.
  2. #2
  3. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by pabloj
    Do you mean the information schema ? It's pretty standard
    Hi Pabloj,

    Not the schema, but a reference facility which centralizes the properties of all elements in the database.

    Am not yet quite into PostgreSQL, but did find out via Google that there's a project proposed to create a D.D. for PostgreSQL.

    So, it seems that it does not have one yet.

    Would still appreciate some more comments or info on this.

    Thanks
  6. #4
  7. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    Originally Posted by m. lopez
    ... a reference facility which centralizes the properties of all elements in the database.
    ...
    could you give us an example? To me the information_schema tables and views do exactly this.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by pabloj
    could you give us an example? To me the information_schema tables and views do exactly this.
    Hi pabloj,

    This'd be the Codd definition:

    ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,
    Rule 4 : Dynamic on-line catalog based on the relational model.

    "The data base description is represented at the logical level in the same way as-ordinary data, so that authorized users can apply the same relational language to its interrogation as they apply to the regular data."

    The Data Dictionary is held within the RDBMS, thus there is no-need for off-line volumes to tell you the structure of the database.
    ,,,,,,,,,,,,,,,,,,

    Basically, it'd be a Table in its own rights, but it'd be a table about the tables and some more.

    And it'd be automatically updated by the various commands that alter the schema of the data base.

    If the schema is a 'report, or listing' produced when needed, and not an actual table, it'd be difficult to consult directly, would say.

    Not being familiar yet on PostgreSQL, am asking if what's in it is indeed called, and qualifies as a data-dictionary or catalog.

    Thanks for any furher clarifications!
  10. #6
  11. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by pabloj
    Yes, it's the information_schema
    Hi pabloj,

    Am happy to hear this.

    Would you confirm the information_schema IS a Table just as any other table, accessible to who has access rights for looking-into (read-only), then, by any other means that data-tables are accessed, but can not be modified other than by schema modifications?

    Thanks again!
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,786
    Rep Power
    349
    Originally Posted by m. lopez
    Would you confirm the information_schema IS a Table
    No it's a SCHEMA as the name says. That is a collection of several tables containing information about all the objects in the database.

    Read the docs at
    http://www.postgresql.org/docs/8.1/s...on-schema.html

    for more information
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by shammat
    No it's a SCHEMA as the name says. That is a collection of several tables containing information about all the objects in the database.

    Read the docs at
    http://www.postgresql.org/docs/8.1/s...on-schema.html

    for more information
    Hi shammat,

    Thanks for the comment!

    Have been finding a little more about this, and what you'd be refering to as information_schema is a way to standardize access to the shcema of the database via SQL Standards.

    However, it does seem that the meta-data or 'data-dictionary' IS actually a set of system-owned tables which start with the 'pg_' identifier.

    The information_schema would then be just a VIEW or 'listing' based on those system tables for whatever purposes.

    If the information_schema is DROPPED, nothing really happens.

    Please correct me if this is not so.

    Thanks again.
  18. #10
  19. Modding: Oracle MsSQL Firebird
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2001
    Location
    Outside US
    Posts
    8,527
    Rep Power
    539
    I don't see your point, are you trying to discuss postresql's (or any other database) implementation of Codd's definition?
    Anyway to understand the information_schema look at part 11 of the SQL standard (more here )
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by pabloj
    I don't see your point, are you trying to discuss postresql's (or any other database) implementation of Codd's definition?
    Anyway to understand the information_schema look at part 11 of the SQL standard (more here )
    Hi pabloj,

    No, not about OTHER databases.

    Just wanted to know about how PostgreSQL does it.

    A am clear on that already, and also on the fact that it is so starting only on version 8 or so.

    Am happy to find this out and appreciate the comments.

    Thanks!
  22. #12
  23. 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
    Clarification on information_schema vs. pg_* tables:

    The pg_* tables are inded the base tables for the PostgreSQL DDL. These have been in PostgreSQL for a long time, definitely before 8.0. The information_schema was created as a set of views on the pg_* tables which are easier to work with, and which meet (for the most part) the SQL standard for DDL.
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  24. #13
  25. 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
    Also, there is a new project for PostgreSQL DDL called NewSysViews which will further expand the DDL capabilities of PostgreSQL. Eventually this will be part of the standard PostgreSQL release.
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
  26. #14
  27. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2000
    Posts
    28
    Rep Power
    0
    Originally Posted by rycamor
    Clarification on information_schema vs. pg_* tables:

    The pg_* tables are inded the base tables for the PostgreSQL DDL. These have been in PostgreSQL for a long time, definitely before 8.0. The information_schema was created as a set of views on the pg_* tables which are easier to work with, and which meet (for the most part) the SQL standard for DDL.
    Hi rycamor,

    Yes! That's the way I gather it exactly, and am sure of what you say about the data having been there already for some time in said tables.

    I also gather that the SQL standard on this is a convenient way to qualify and standardize on the Codd rule, which was/is being addressed in various designs in various ways, and that compliance is recent, althugh apparently not a requirement.

    The data, it is clear, is not stored in 'schemas', as schemas are basically a definition. Data about the database is still in tables, as usual.

    Thanks for your reply.
  28. #15
  29. 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 m. lopez
    Hi rycamor,

    Yes! That's the way I gather it exactly, and am sure of what you say about the data having been there already for some time in said tables.

    I also gather that the SQL standard on this is a convenient way to qualify and standardize on the Codd rule, which was/is being addressed in various designs in various ways, and that compliance is recent, althugh apparently not a requirement.
    Yes, this rule by Codd could be viewed as a logical extension of rules 1 (the Information Principal) and 2 (Guaranteed Access). As long as this is information about a relational system, it should be accessible in a relational manner.

    The data, it is clear, is not stored in 'schemas', as schemas are basically a definition. Data about the database is still in tables, as usual.
    There is unfortunately some confusion about what the term "schema" means, which is why I tend to avoid it. In version 7 and up, PostgreSQL supported the SQL concept of "schemas", which means essentially support for multiple namespaces in a single database, so it is possible to have two tables of the same name, inhabiting different named schemas, for example. The more general and informal meaning of "schema" in the database world is that it refers to a database design or model, or table layout, if you will. Pabloj and shammat were referring to the specific PostgreSQL-supported usage.

    (Just as confusing in the database world is the double use of the term 'database' to mean a DBMS, as well as a specific instance of a collection of tables running inside a DBMS )
    The real n-tier system:

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

    Amazon wishlist -- rycamor (at) gmail.com
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo