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.