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 ésto 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.
Pingback: Simulando show tables y describe de Mysql en PostgreSql - DbRunas
En Postgres se usa:
\d
\d nombredeTabla
@Luis
Es cierto. Desde la consola de PostgreSql tenés a mano esas dos funciones que generan salidas muy similares tanto a los queries que planteo como a las funciones nativas de Mysql.
El uso de los queries al information_schema tiene dos puntos que quizás sean válidos para explicar el tip: personalización de la información a mostrar y acceso a esa información más allá de la consola (por ejemplo, desde el lenguaje de porgramación que estemos utilizando).
Saludos.
Buenas,
y como hago para añadir que columna (campo) es clave o clave foranea ?
Gracias y Un Saludo
@Jose
Hacé el siguiente query.
select * from information_schema.constraint_column_usage;De esa forma vas a obtener las constraints de cada tabla.
Saludos.
Gracias,
de todos modos he averiguado que con esta sentencia obtenemos las “constraints” de una tabla:
select r.relname as “Table”, c.conname as “Constraint Name”,
contype as “Constraint Type”, conkey as “Key Columns”,
confkey as “Foreign Columns”, consrc as “Source”
from pg_class r, pg_constraint c
where r.oid = c.conrelid
and relname = ‘nombre_tabla’;
Saludox
@Jose:
Gracias por el dato.
Quizás habría que trabajar con lo que dan ambas columnas para armar una más completa.
;-)
Muy buen aporte a los que necesitamos este tipo de informacion. Algunos compañeros mios de la universidad buscan lo mismo que yo en esta pagina…
Muchas gracias
Muy buen aporte! Muchísimas gracias!