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'));