Author: sjg

Starting at the top, PostgreSQL and MySQL have wildly different concepts of what constitutes a schemata. In MySQL information_schema is a database that can be used directly or via cross-database queries. Whereas in PostgreSQL, you cannot perform cross-database queries because you connect to a single database. Here, the information_schema is contained within what PostgreSQL calls a schema, or a seperate namespace within a database.

Common Fields (schemata):
catalog_name
    PostgreSQL: Always the name of the parent database
    MySQL: Always NULL
schema_name
    PostgreSQL: Name of the schema
    MySQL: Name of a database
default_character_set_name
    PostgreSQL: Feature not available
    MySQL: Default character set (utf8, latin1, etc.)

PostgreSQL:
schema_owner
    User (role) that owns the schema

MySQL:
default_collation_name
    Default character set collation (utf8_general_ci ...)

So, what we get out of all this is that for PostgreSQL, we connect to a database and schemata tells us what schema’s exist. In the case of MySQL, it tells us what databases exist. Of course application dependant, but in many cases you can treat these as the same thing.

Once we know all about our databases, or whether we wanted to know about them at all, the next thing we will probably want to dig into are tables.

Common Fields (tables):
table_catalog
    PostgreSQL: Always the name of the parent database
    MySQL: Always NULL
table_schema
    PostgreSQL: Name of the schema that contains the table
    MySQL: Name of the database
table_name
    Name of the table
table_type
    'BASE TABLE' for a regular persistent table
    'VIEW' for a view
    PostgreSQL: 'LOCAL TEMPORARY' for a temporary table
    MySQL: 'TEMPORARY' for a temporary table

MySQL:
table_comment
    Comment
   
To set comments on PostgreSQL tables:
    COMMENT ON TABLE table_name IS 'This is a table comment';
To retrieve comments we must dig into the system information schema:
    SELECT obj_description(
        (SELECT c.oid FROM pg_catalog.pg_class c
         WHERE c.relname='table_name'), 'pg_class')
         AS comment;

Next, we’ll want to dig into information about the columns of those tables

Common Fields (tables):
table_catalog
    PostgreSQL: Always the name of the parent database
    MySQL: Always NULL
table_schema
    PostgreSQL: Name of the schema that contains the table
    MySQL: Name of the database
table_name
    Name of the table
column_name
    Name of the column
ordinal_position
    Position of the column within the table, the ordering of the columns
column_default
    Default (expression) for the column
is_nullable
    Whether or not the column can be null, 'YES' or 'NO'
data_type
    Name of the built-in type
    PostgreSQL:
        'ARRAY' - see the element_types view
        'USER-DEFINED' - identified in udt_name+
        If domain-based, refers to the underlying
        type and the domain is identified in domain_name+
character_maximum_length
    Maximum length of character data if defined, otherwise NULL
character_set_name+collation_name
    MySQL: NULL if not defined

PostgreSQL:
udt_schema
    Schema the user-defined type is defined in
udt_name
    Name of the user-defined type

domain_schema
    Schema the domain is defined in
domain_name
    Name of the domain
   
MySQL:
column_type
    More specific column type than data_type (varchar(255)
column_key
     'PRI' or blank ?
column_comment
    Comment

To set comments on PostgreSQL columns:
    COMMENT ON COLUMN table_name.column_name IS 'This is a column comment';
To retrieve comments we must dig into the system information schema:
    SELECT col_description(
        (SELECT c.oid FROM pg_catalog.pg_class c
        WHERE c.relname='table_name'),
        (SELECT a.attnum
        FROM pg_catalog.pg_class c,
             pg_catalog.pg_attribute a
        WHERE a.attrelid=c.oid
        AND c.relname='table_name'
        AND a.attname='column_name'));