Simulando show tables y describe de Mysql en PostgreSql

Si hay algo que me toca extrañar de Mysql cuando trabajo con PostgreSql (en particular cuando estoy haciéndolo por consola y de forma remota), son los dos comandos que ayudan a armar los queries que vamos necesitando.

A pesar que desde Mysql 5 existe el catálogo con la metadata de las bases de datos, para quienes venimos desde versiones previas, el uso de funciones como show tables y describe han sido casi fundamentales al momento de trabajar con la base de datos.

Para quienes no conozcan los comandos, el primero nos lista las tablas que existen en nuestra base de datos, y el segundo, pasándole como valor el nombre de una tabla, nos muestra qué columnas tiene, con qué tipo de datos, información de los índices y algunos campos más.

Vayamos a los ejemplos usando una la base de datos de Magento.

mysql> show tables;
+--------------------------------------------+
| Tables_in_magento                          |
+--------------------------------------------+
| admin_assert                               |
| admin_role                                 |
| admin_rule                                 |
| admin_user                                 |
| adminnotification_inbox                    |
| amazonpayments_api_debug                   |
| api_assert                                 |
| api_role                                   |
| api_rule                                   |
| api_session                                |
| api_user                                   |
| catalog_category_entity                    |
| ...                                        |
+--------------------------------------------+

La cantidad de tablas es bastante más amplia, para el ejemplo mostramos sólo una parte.

Ahora bien, tengo que hacer una consulta que me devuelva sólo algunos valores, pero no recuerdo bien los campos. Aquí es donde describe nos va a ayudar. Supongamos que es la tabla catalog_category_entity la que necesitamos usar y no sabemos nada de sus campos.

mysql> describe catalog_category_entity;
+------------------+----------------------+------+-----+---------------------+----------------+
| Field            | Type                 | Null | Key | Default             | Extra          |
+------------------+----------------------+------+-----+---------------------+----------------+
| entity_id        | int(10) unsigned     | NO   | PRI | NULL                | auto_increment |
| entity_type_id   | smallint(8) unsigned | NO   |     | 0                   |                |
| attribute_set_id | smallint(5) unsigned | NO   |     | 0                   |                |
| parent_id        | int(10) unsigned     | NO   |     | 0                   |                |
| created_at       | datetime             | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at       | datetime             | NO   |     | 0000-00-00 00:00:00 |                |
| path             | varchar(255)         | NO   |     | NULL                |                |
| position         | int(11)              | NO   |     | NULL                |                |
| level            | int(11)              | NO   | MUL | NULL                |                |
| children_count   | int(11)              | NO   |     | NULL                |                |
+------------------+----------------------+------+-----+---------------------+----------------+
10 rows in set (0.00 sec)

Como ven, nos da el detalle de la tabla y ahora si, no sólo conocemos los campos posibles, sino el tipo de dato y si tienen algún índice.

De aquí en más, será bastante más sencillo seguir trabajando con nuestros queries.

Hasta acá todo muy lindo pero, como decía al comienzo, cuando toca trabajar con PostgreSql éstas dos funciones se extrañan; aunque gracias a la metadata que nos brinda, podemos resolver esto con algunas consultas al information_schema.

Si bien sólo voy a abordar la forma de emular las dos funciones que mencioné, seria recomendable que hagan una revisión completa para conocer mejor la información que puede obtenerse.

Vamos con el primer caso: obtener la lista de tablas de una base de datos.

SELECT
  tables.table_name,
  pg_description.description
FROM
  information_schema.tables
  LEFT JOIN
    pg_class ON tables.table_name::name = pg_class.relname
  LEFT JOIN
    pg_description ON pg_class.oid = pg_description.objoid AND pg_description.objsubid = 0
WHERE
  tables.table_schema::text = 'public'::text AND tables.table_type = 'BASE TABLE'::text
ORDER BY
  tables.table_name ASC;

El resultado de ésta consulta, será lo siguiente:

  table_name   |                   description
---------------+-------------------------------------------------
 errores       | Errores del servidor
 logs          | Logs de tareas de backend del sistema
(2 rows)

A diferencia de lo que se obtiene con la versión original de Mysql, en el caso de PostgreSql he agregado el comentario de la tabla, pero es un detalle personal y dependerá de que tengan los comentarios.

Otra aclaración es que el show tables de Mysql devuelve tanto tablas como vistas. En la consulta que hicimos en PostgreSql, he filtrado para que sólo devuelva las tablas (es la condición que indica tables.table_type = ‘BASE TABLE’). Si quisiéramos que en PostgreSql nos muestre las vistas, o bien deberíamos aplicarlo a la condición o bien quitar el filtro de tipo de tabla.

La segunda función a simular es la que nos describe la composición de una tabla. Nuevamente, vamos a recurrir a una consulta y me voy a basar en la tabla errores que me devolvió la consulta anterior.

SELECT
  columns.column_name,
  columns.data_type,
  columns.column_default,
  columns.is_nullable,
  columns.character_maximum_length,
  columns.numeric_precision,
  pg_description.description,
  columns.table_name
FROM
  information_schema.columns
  LEFT JOIN
    pg_class ON columns.table_name::name = pg_class.relname
  LEFT JOIN
    pg_description ON pg_class.oid = pg_description.objoid AND columns.ordinal_position::integer = pg_description.objsubid
WHERE
  columns.table_schema::text = 'public'::text
  AND
  columns.table_name = 'errores'
ORDER BY
  columns.table_name ASC,
  columns.ordinal_position ASC
;

El resultado de ésta consulta, que aplica una condición pidiendo solo los campos de una tabla determinada, seria el siguiente.

   column_name   |          data_type          |             column_default           | is_nullable | character_maximum_length | numeric_precision | description | table_name
-----------------+-----------------------------+--------------------------------------+-------------+--------------------------+-------------------+-------------+------------
 id              | integer                     | nextval('seq_ierrores_id'::regclass) | NO          |                          |                32 |             | errores
 fecha           | timestamp without time zone |                                      | YES         |                          |                   |             | errores
 usuario         | character varying           |                                      | YES         |                       50 |                   |             | errores
 nerror          | character varying           |                                      | YES         |                      100 |                   |             | errores
 descripcionasp  | character varying           |                                      | YES         |                      255 |                   |             | errores
 categoria       | character varying           |                                      | YES         |                      255 |                   |             | errores
 columna         | smallint                    |                                      | YES         |                          |                16 |             | errores
 descripcion     | character varying           |                                      | YES         |                      255 |                   |             | errores
 archivo         | character varying           |                                      | YES         |                      255 |                   |             | errores
 linea           | smallint                    |                                      | YES         |                          |                16 |             | errores
 numero          | bigint                      |                                      | YES         |                          |                64 |             | errores
 codigofuente    | character varying           |                                      | YES         |                      255 |                   |             | errores
 revisado        | boolean                     | false                                | YES         |                          |                   |             | errores
 usuariorevision | smallint                    |                                      | YES         |                          |                16 |             | errores
 fecharevision   | timestamp without time zone |                                      | YES         |                          |                   |             | errores
(21 rows)

Si prestan atención, ya tenemos los mismos resultados (detalle más, detalle menos) que obteníamos al comienzo con funciones nativas.

Sólo restan un par de aclaraciones. La última consulta trajo solamente los campos de una tabla determinada porque aplicamos un filtro. Habitualmente, lo que suelo hacer es crear, al menos, dos vistas con éstas consultas. En el caso del último ejemplo, no aplico filtro en la consulta, pero al momento de seleccionar la vista, aplicamos una condición donde filtramos por la tabla que queremos consultar.

Unite a la lista de suscriptores

Una vez por mes vas a recibir un mail con contenido que se relaciona con lo que vemos en el blog, que extiende o anticipa lo que hacemos en Twitch, y que también suele incluir anécdotas del MundoReal® y algún que otro link.

Es gratis, no tiene publicidad y con el double opt-in de Mailchimp.