List:General Discussion« Previous MessageNext Message »
From:Jochem van Dieten Date:August 11 2004 5:03pm
Subject:Re: [OT] PostgreSQL / MySQL Data Dictionary
View as plain text  
On Wed, 11 Aug 2004 11:04:01 -0500, Josh Trutwin wrote:
> On Wed, 11 Aug 2004 17:20:45 +0200 Jochem van Dieten wrote:
>> 
>> SHOW TABLES does not make sense. How are you going to join the
>> output of SHOW TABLES against the output of SHOW COLUMNS and SHOW
>> INDEXES?
>>
>> SELECT * FROM INFORMATION_SCHEMA.TABLES makes sense.
>>
>>
>> And as for easy remembering: I prefer to remember just one standard,
>> instead of the idiosyncracies of each product.
> 
> Yes, a queryable (sp?) set of dictionary tables/views would be nice for doing this. 
> The MySQL set of "SHOW" commands is pretty painful for any serious development.
> 
> Does PostgreSQL have a set of information schema tables to query against like Oracle
> does (e.g. SELECT table_name FROM user_tables)?

It does have a system that is pretty comparable to what is in the the
SQL standard. There is a pg_catalog schema that contains the base
tables (information about database objects in PostgreSQL is stored in
normal tables). Although not recommended (to say the least), these
base tables can even be updated using SQL.
As a queryable front end there is a read-only information_schema
schema that contains many views describing the objects in the
database. It is somewhat similar to what Oracle has, but it is exactly
the same as what the SQL standard has.

Since MS SQL Server implements the same part of the SQL standard
metadata queries are quite portable between PostgreSQL and MS SQL
Server.


> I noticed this from a quick google search:
> 
> http://gborg.postgresql.org/project/orapgsqlviews/projdisplay.php
> 
> Is something similar planned for MySQL in the future?  Doesn't appear to be from:
> http://dev.mysql.com/doc/mysql/en/Roadmap.html

MySQL strives to ful SQL standard compliance (ISO/IEC 9075), so an
INFORMATION_SCHEMA must be planned for someday. However, that day does
not appear to be in the near future.

I wonder how far one would be able to mimic one once MySQL 5.1 is out
by creating a database named INFORMATION_SCHEMA, which contains views
which call all the SHOW commands. That would probably need some
serious work to make the SHOW commands joinable, but the naming
convention might work.

Jochem
Thread
Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLBrad Tilley10 Aug
    • Re: Difference between PostgreSQL and MySQLJosh Trutwin10 Aug
      • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
        • Re: Difference between PostgreSQL and MySQLJosh Trutwin11 Aug
          • Re: Difference between PostgreSQL and MySQLJochem van Dieten11 Aug
            • Re: [OT] PostgreSQL / MySQL Data DictionaryJosh Trutwin11 Aug
              • Re: [OT] PostgreSQL / MySQL Data DictionaryJochem van Dieten11 Aug
                • MySQL Data Dictionary (INFORMATION_SCHEMA)Jim Winstead11 Aug
                  • Re: MySQL Data Dictionary (INFORMATION_SCHEMA)Jochem van Dieten11 Aug
                  • Re: MySQL Data Dictionary (INFORMATION_SCHEMA)Josh Trutwin11 Aug
          • Changing Fulltext defaults in Win32leegold11 Aug
  • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLSGreen10 Aug
    • Re: Difference between PostgreSQL and MySQLMartijn Tonies10 Aug
    • Re: Difference between PostgreSQL and MySQLJochem van Dieten10 Aug
      • Re: Difference between PostgreSQL and MySQLJosh Trutwin11 Aug
  • Re: Difference between PostgreSQL and MySQLMartijn Tonies10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLEWAGW10 Aug
  • Re: Difference between PostgreSQL and MySQLmos11 Aug
    • RE: Difference between PostgreSQL and MySQLLachlan Mulcahy11 Aug
      • On the licensing once againDebugasRu11 Aug
        • Re: On the licensing once againZak Greant11 Aug
        • RE: On the licensing once againLachlan Mulcahy11 Aug
          • R: On the licensing once againLeonardo Francalanci11 Aug
            • Re: R: On the licensing once againDebugasRu11 Aug
              • RE: R: On the licensing once againLachlan Mulcahy11 Aug
          • Re: On the licensing once againIssac Goldstand12 Aug
            • Re: On the licensing once againZak Greant12 Aug
  • Converting access to mysql dbEWAGW11 Aug
  • Re: Changing Fulltext defaults in Win32Cemal Dalar11 Aug
    • Re: Changing Fulltext defaults in Win32leegold11 Aug
  • Re: Changing Fulltext defaults in Win32Cemal Dalar11 Aug
  • Re: [OT] PostgreSQL / MySQL Data DictionaryPeter  Brawley11 Aug
    • RE: Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team11 Aug
      • Re: [OT] PostgreSQL / MySQL Data DictionaryEamon Daly11 Aug
        • RE: Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug
          • Re: [OT] PostgreSQL / MySQL Data DictionaryEgor Egorov12 Aug
            • RE: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug
Re: On the licensing once againZak Greant11 Aug
Re: On the licensing once againLachlan Mulcahy11 Aug
  • R: On the licensing once againLeonardo Francalanci11 Aug
    • Re: R: On the licensing once againSantino11 Aug
      • Re: R: On the licensing once againZak Greant12 Aug
    • Re: R: On the licensing once againZak Greant12 Aug
Re: On the licensing once againLeonardo Francalanci11 Aug
  • Re: On the licensing once againRich Lafferty11 Aug
Re: [OT] PostgreSQL / MySQL Data DictionarySciBit MySQL Team12 Aug