Guía de information_schema de PostgreSQL para administrar bases de datos

Introducción

Si te encargas de tareas de administración de bases de datos, como crear tablas, ver relaciones o incluso generar reportes, entender el information_schema de PostgreSQL te puede ser muy útil. En este tutorial veremos los fundamentos del information_schema de PostgreSQL con algunos ejemplos de código.

Este tutorial está escrito principalmente para PostgreSQL, aunque algunos conceptos pueden aplicarse a otras bases de datos relacionales SQL.

Antes de que sigas leyendo, quería contarte sobre nuestro gestor de bases de datos PostgreSQL moderno y rápido, Beekeeper Studio. Explorar esquemas con él es pan comido, y es gratis para PostgreSQL. Descárgalo aquí.

¿Qué es el information_schema?

El information_schema es una función esencial de las bases de datos PostgreSQL que da acceso a los metadatos de la ‘base de datos actual’. Básicamente es un conjunto de vistas que contienen información sobre los distintos objetos de la ‘base de datos actual’. Desde tablas hasta columnas y restricciones, el information_schema de PostgreSQL te muestra los detalles de la arquitectura de una base de datos.

SELECT *
FROM information_schema.tables;

Si ejecutas el comando anterior en el editor SQL de Beekeeper Studio, por ejemplo, deberías ver una lista de las tablas de tu base de datos actual.

Profundizando

Ver tablas públicas

Para listar todas las tablas públicas disponibles en tu base de datos actual, solo ejecuta:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema='public';

Este comando devuelve una lista con los nombres de todas las tablas de tu ‘base de datos actual’ en el esquema ‘public’.

Resultado usando la base de datos de ejemplo ‘chinook’

Inspeccionar columnas

Usa la vista information_schema.columns para obtener información detallada sobre las columnas de una tabla en particular:

SELECT column_name, data_type 
FROM information_schema.columns 
WHERE table_name ='album';

Este código devuelve una lista con los nombres de todas las columnas y sus tipos de datos para la tabla que elegiste.

Resultado usando la base de datos de ejemplo ‘chinook’

Mostrar restricciones

La vista information_schema.table_constraints proporciona información sobre las restricciones definidas en la tabla.
Puedes usar la siguiente consulta para obtener las restricciones de una tabla específica:

SELECT constraint_name, constraint_type 
FROM information_schema.table_constraints 
WHERE table_name='album';

Al ejecutar el comando obtendrás los nombres y tipos de restricciones de ‘your_table’, lo que te da una idea de las reglas que se aplican en esa tabla.

Resultado usando la base de datos de ejemplo ‘chinook’

Conclusión

El information_schema de PostgreSQL es una herramienta importante para explorar y entender la estructura y los detalles de tu base de datos. Cómo lo uses depende de tus necesidades y de la configuración de tu base de datos. Recuerda reemplazar los nombres de tablas y columnas de ejemplo por los de tu base de datos para que los comandos se ejecuten correctamente.

Nota: Todos los ejemplos de este tutorial están pensados para PostgreSQL (versiones 9.4 a 13.3) y podrían no ser compatibles con otros motores de bases de datos. Asegúrate siempre de adaptar tus consultas al motor de base de datos que estés usando. El information_schema de PostgreSQL puede hacer que explorar tu base de datos sea más fácil, más efectivo y te tome mucho menos tiempo.