PostgreSQL proporciona de manera sencilla toda la información de los objetos que se encuentran en una base de datos a través de su INFORMATION SCHEMA.

Al consultar el INFORMATION_SCHEMA de PostgreSQL (que no es más que una serie de vistas que nos muestran la estructura de nuestras bases de datos) nos encontraremos toda la información referente tablas, vistas y campos contenidos dentro de las mismas.

Las siguientes tablas son las que usaremos para generar nuestro diccionario de datos:

• PG_CLASS.
• TABLES.
• COLUMNS.
• TABLE_CONSTRAINTS.
• KEY_COLUMN_USAGE.
• REFERENTIAL_CONSTRAINTS.
• CONSTRAINT_COLUMN_USAGE.

Obtener las tablas y vistas de una base de datos en PostgreSQL

Para obtener las tablas y vistas contenidas dentro de una base de datos en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.TABLES y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos TABLE_NAME (nombre de la tabla) y la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla), filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos), todo ello ordenado por TABLE_NAME (nombre de la tabla):

SELECT 
    t1.TABLE_NAME AS tabla_nombre,
    PG_CATALOG.OBJ_DESCRIPTION(t2.OID, 'pg_class') AS tabla_descripcion
FROM 
    INFORMATION_SCHEMA.TABLES t1
    INNER JOIN PG_CLASS t2 ON (t2.relname = t1.table_name)
WHERE 
    t1.TABLE_SCHEMA='[NOMBRE_ESQUEMA]'
ORDER BY
    t1.TABLE_NAME;

Obtener las columnas de una tabla o vista de una base de datos en PostgreSQL

Para obtener las columnas de una tabla en PostgreSQL, vamos a acceder a la tabla INFORMATION_SCHEMA.COLUMNS y PG_CLASS (para obtener el OID de la tabla), y listaremos los campos COLUMN_NAME (nombre de la columna), COLUMN_DEFAULT (valor por defecto de la columna), IS_NULLABLE (si la columna acepta o no valores nulos), DATA_TYPE (tipo de dato de la columna), NUMERIC_PRECISION ó CHARACTER_MAXIMUM_LENGTH (precisión del tipo de dato de la columna), la función PG_CATALOG.OBJ_DESCRIPTION (para obtener comentario de la tabla) y DOMAIN_NAME (nombre del dominio de la columna), esta vez filtrando por el campo TABLE_SCHEMA (nombre del esquema de la base de datos) y TABLE_NAME (nombre de la tabla), todo ello ordenado por ORDINAL_POSITION (orden de nuestra columna en la tabla):

SELECT
    t1.COLUMN_NAME AS columna_nombre,
    t1.COLUMN_DEFAULT AS columna_defecto,
    t1.IS_NULLABLE AS columna_nulo,
    t1.DATA_TYPE AS columna_tipo_dato,
    COALESCE(t1.NUMERIC_PRECISION,
    t1.CHARACTER_MAXIMUM_LENGTH) AS columna_longitud,
    PG_CATALOG.COL_DESCRIPTION(t2.OID,
    t1.DTD_IDENTIFIER::int) AS columna_descripcion,
    t1.DOMAIN_NAME AS columna_dominio
FROM 
    INFORMATION_SCHEMA.COLUMNS t1
    INNER JOIN PG_CLASS t2 ON (t2.RELNAME = t1.TABLE_NAME)
WHERE 
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]'
ORDER BY
    t1.ORDINAL_POSITION;

Obtener las restricciones de las columnas de una tabla de una base de datos en PostgreSQL

Para obtener restricciones primarias y foráneas de las tablas de una base de datos en PostgreSQL, vamos a acceder a las tablas INFORMATION_SCHEMA.TABLE_CONSTRAINTS, INFORMATION_SCHEMA.KEY_COLUMN_USAGE, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS y INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE, y listaremos los campos CONSTRAINT_TYPE (tipo de restricción de la columna), COLUMN_NAME (nombre de la columna), CONSTRAINT_NAME (nombre de la restricción de la tabla y nombre de la restricción de la tabla referenciada), TABLE_NAME (nombre de tabla referenciada) y COLUMN_NAME (nombre de columna de tabla referenciada), filtrando por el campo TABLE_SCHEMA (nombre de base de datos), TABLE_NAME (nombre de la tabla) y COLUMN_NAME (nombre de la columna), todo ello ordenado por CONSTRAINT_TYPE (tipo de restricción):

SELECT
    t1.CONSTRAINT_TYPE AS tipo_restriccion,
    t2.COLUMN_NAME AS columna_nombre,
    t1.CONSTRAINT_NAME AS restriccion_nombre,
    t4.CONSTRAINT_NAME AS restriccion_referencia_nombre,
    t4.TABLE_NAME AS tabla_referencia,
    t4.COLUMN_NAME AS columna_referencia
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS t1
    LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE t2
    ON t1.CONSTRAINT_CATALOG = t2.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t2.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t2.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS t3
    ON t1.CONSTRAINT_CATALOG = t3.CONSTRAINT_CATALOG
    AND t1.CONSTRAINT_SCHEMA = t3.CONSTRAINT_SCHEMA
    AND t1.CONSTRAINT_NAME = t3.CONSTRAINT_NAME
    LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE t4
    ON t3.UNIQUE_CONSTRAINT_CATALOG = t4.CONSTRAINT_CATALOG
    AND t3.UNIQUE_CONSTRAINT_SCHEMA = t4.CONSTRAINT_SCHEMA
    AND t3.UNIQUE_CONSTRAINT_NAME = t4.CONSTRAINT_NAME
WHERE
    t1.TABLE_SCHEMA = '[NOMBRE_ESQUEMA]' AND
    t1.TABLE_NAME = '[NOMBRE_TABLA]' AND
    t2.COLUMN_NAME = '[NOMBRE_COLUMNA]'
ORDER BY
    t1.CONSTRAINT_TYPE DESC;

Pueden explorar muchas más posibilidades creando nuevas consultas sobre la base de datos INFORMATION_SCHEMA. Si ésta entrada les ha sido de utilidad también les muestro una forma de generar un diagrama entidad relación de manera automatizada.

También pueden ver en mi blog cómo generar un diccionario de datos de una base de datos en MySQL.