PostgreSQL

PostgreSQL es un gestor que trabaja con bases de datos relacionales y que está orientado a objetos. Se trata de un programa de código abierto u open source, es decir, no está bajo el control de ninguna compañía particular, sino que cuenta con una comunidad de desarrolladores que trabajan en mejorar el programa de forma desinteresada.

Historia

El sistema de gestión de bases de datos PostgreSQL, también llamado Posgres, tiene su origen en el año 1982, como un proyecto desarrollado en la Universidad de Berkeley. Denominado inicialmente Ingres, este proyecto fue liderado por Michael Stonebaker y fue uno de los primeros en tratar de crear una base de datos relacional.

Ingres fue la base para otro proyecto iniciado por Stonebaker en 1985, llamado POSTGRES. Durante nueve años de desarrollaron 4 versiones comerciales de POSTGRES, hasta que en el año 1994 el grupo de desarrolladores que lo habían llevado a cabo se disolvió.

Sin embargo, el trabajo hecho hasta el momento no cayó en el olvido, y otros dos miembros de la universidad, Andrew Yu y Jolly Chen, continuaron con la labor de sus predecesores trabajando sobre la base de POSTGRES. A ellos se les unieron más tarde otras personas ajenas a la universidad, como Marc Fournier, Bruce Momjian y Vadim B. Mikheev

En 1996, decidieron cambiar el nombre del programa, de tal manera que reflejase la principal característica del programa, esto es, la utilización del lenguaje SQL. Así es como surgió su nombre definitivo, y que mantiene hoy en día, PostgreSQL. En 1997 fue lanzada la primera versión comercial de PostgreSQL y desde entonces hasta el día de hoy muchos otros voluntarios han participado en el desarrollo del motor y mejorado sus características.

Características principales

PostgreSQL es un sistema de base de datos relacional de alta disponibilidad. Es capaz de funcionar de manera estable en el servidor y, por lo tanto, resulta robusto, una de las principales características que buscan las empresas. Además, es consistente y tolerante a fallos. Es compatible con el modelo relacional, ya que asegura siempre su integridad referencial.

Algunas de sus principales características son:

• Alta concurrencia. Es capaz de atender a muchos clientes al mismo tiempo y entregar la misma información de sus tablas, sin bloqueos.
• Soporte para múltiples tipos de datos de manera nativa. Ofrece los tipos de datos habituales en los sistemas gestores, pero además muchos otros que no están disponibles en otros competidores, como direcciones IP, direcciones MAC, Arrays, números decimales con precisión configurable, figuras geométricas, etc.
• Soporte a triggers. Permite definir eventos y generar acciones cuando estos se disparan.
• Trabajo con vistas. Esto quiere decir que pueden consultar los datos de manera diferente al modo en el que se almacenan.
• Objeto-relacional. Otra de sus principales características, que permite trabajar con sus datos como si fueran objetos y ofrece mecanismos de la orientación a objetos, como herencia de tablas.
• Soporte para bases de datos distribuidas. Donde el trabajo con transacciones asegura que estas tendrán éxito cuando han podido realizarse en todos los sistemas involucrados.
• Soporte para gran cantidad de lenguajes. PostgreSQL es capaz de trabajar con funciones internas, que se ejecutan en el servidor, escritas en diversos lenguajes como C, C++, Java, PHP, Python o Ruby. Además, ofrece interfaces para ODBC y JDBC, así como interfaces de programación para infinidad de lenguajes de programación.

En esta práctica vamos a realizar la instalación de PostgreSQL tanto en Ubuntu Server 22.04 como en Windows 10 y además realizaremos la instalación y configuración de la herramienta PgAdmin4 para gestionar y administrar PostgreSQL. 

Para llevar a cabo la práctica os adjunto el video donde os muestro paso a paso como realizar la instalación y los links a las páginas oficiales donde encontraréis el código a emplear.

• PostgreSQL
• PgAdmin4

UBUNTU SERVER - POSTGRESQL

En el siguiente video os muestro como instalar y configurar PostgreSQL en Windows 10. Conectándonos tanto con PGADMIN4 como con SQL DEVELOPER para administrar nuestro SGBD de forma remota. Además, configuramos PostgreSQL para que admita conexiones desde equipos remotos ya que por defecto solo admite conexiones desde Localhost.

WINDOWS 10 - POSTGRESQL

En el siguiente video os muestro como crear una base de datos relacional desde 0 en PostgreSQL con pgAdmin4. Creando tablas, atributos, claves primarias, claves foráneas y atributos autonuméricos.

POSTGRESQL - CREAR BASE DE DATOS

DDL (Data Definition Language) es una parte de SQL (Lenguaje de Consulta Structurada) que se utiliza para definir y modificar la estructura de una base de datos. Incluye comandos como CREATE, ALTER, DROP, etc. que permiten crear, modificar y eliminar objetos en una base de datos, como tablas, índices, vistas, procedimientos almacenados y otros.

Con los comandos DDL, se pueden definir las características de las tablas, como el nombre de las columnas, el tipo de datos de cada columna, las restricciones en los datos, como claves primarias y claves externas, entre otros aspectos.

Algunos ejemplos de comandos DDL son:

• CREATE TABLE: Crea una nueva tabla en la base de datos.
• ALTER TABLE: Modifica la estructura de una tabla existente.
• DROP TABLE: Elimina una tabla de la base de datos.

Es importante tener en cuenta que los cambios realizados a la estructura de una base de datos con comandos DDL son permanentes y pueden afectar el funcionamiento de la aplicación que utiliza la base de datos, por lo que deben ser utilizados con precaución.

En esta práctica aprendemos a crear una base de datos relacional sobre la industria del cine mediante el uso de comandos DDL: CREATE, ALTER, DROP, RENAME. Además, aprendemos a crear tablas, modificarlas o eliminarlas y crear las claves foráneas que relacionan dichas tablas. 

POSTGRESQL - DDL

IMPORTAR CSV

CSV (Comma-Separated Values, Valores Separados por Comas) es un tipo de archivo de texto simple que se utiliza para almacenar y compartir datos tabulares. En un archivo CSV, cada línea representa un registro o fila de datos y cada campo o columna de datos se separa mediante una coma o un carácter separador especificado.

Los archivos CSV son un formato ampliamente utilizado para el intercambio de datos entre diferentes aplicaciones y sistemas, ya que son fáciles de leer y escribir y pueden ser abiertos y editados en un editor de texto o una hoja de cálculo. Además, los archivos CSV son compatibles con la mayoría de los sistemas de bases de datos y herramientas de análisis de datos, lo que los hace ideales para la importación y exportación de datos.

La importación gráfica de archivos CSV delimitados por comas en UTF-8 a PostgreSQL se puede realizar con la herramienta de administración de bases de datos PGAdmin. Los siguientes son los pasos generales para realizar esta tarea:

1. Acceder a PGAdmin: Abra PGAdmin y conéctese a la base de datos en la que desea importar los datos.
2. Crear una tabla: antes de importar los datos, es necesario crear una tabla en la base de datos con la misma estructura que el archivo CSV.
3. Acceder a la opción de importación: Haga clic derecho en la tabla que acaba de crear y seleccione la opción "Importar / Exportar" en el menú desplegable.
4. Seleccionar el archivo CSV: en la siguiente pantalla, seleccione la opción "Importar" y busque el archivo CSV que desea importar.
5. Configurar la importación: en la siguiente pantalla, especifique el tipo de delimitador (coma), la codificación (UTF-8), y si el archivo CSV contiene una fila de encabezado.
6. Iniciar la importación: haga clic en el botón "Ejecutar" para iniciar la importación. PGAdmin mostrará una barra de progreso y una ventana de registro que muestra el progreso y cualquier error durante la importación.
7. Verificación de los datos importados: una vez que se ha completado la importación, se pueden verificar los datos importados abriendo la tabla en PGAdmin y examinando sus contenidos.

Es importante tener en cuenta que la importación gráfica es una forma fácil y rápida de importar datos a PostgreSQL, pero puede ser más lenta que la importación mediante comandos de línea de comandos. Además, dependiendo del tamaño del archivo CSV, la importación puede requerir mucho tiempo y recursos del sistema.

En esta práctica aprendemos a importar ficheros CSV de manera gráfica en nuestro sistema gestor de bases de datos PostgreSQL. Exportando los datos de un fichero Access a un fichero Excel para posteriormente obtener el fichero CSV con los datos delimitados por comas.

POSTGRESQL - IMPORT CSV

EXCEL

Conectar Excel con PostgreSQL es una técnica que permite acceder a los datos de una base de datos PostgreSQL desde Microsoft Excel. Esto permite a los usuarios analizar y manipular los datos en una hoja de cálculo, lo que puede ser útil para tareas como la generación de informes, la creación de gráficos y la realización de análisis de datos. Esta conexión puede ser establecida mediante el uso de un controlador ODBC (Open Database Connectivity), que actúa como intermediario entre Excel y PostgreSQL.

En esta práctica aprendemos a conectar Excel a nuestra base de datos en PostgreSQL. Además, aprendemos a la creación de VISTAS para guardar nuestras consultas en nuestra base de datos y acceder a ellas fácilmente.

POSTGRESQL - EXCEL

AUTOMATIZAR IMPORTACIÓN CSV

Para automatizar la importación de ficheros CSV en nuestra base de datos vamos a hacer uso de PSQL y el comando COPY.

PSQL.exe se utiliza para interactuar con bases de datos PostgreSQL en entornos de línea de comandos de Windows. Al igual que PSQL en sistemas operativos Unix y Linux, PSQL.exe permite a los usuarios ejecutar comandos SQL y realizar tareas de administración de bases de datos, como la creación de tablas, la inserción de datos y la realización de consultas complejas en sistemas operativos Windows. En resumen, PSQL.exe es una herramienta útil para interactuar con bases de datos PostgreSQL en sistemas operativos Windows mediante la línea de comandos.

COPY es un comando de PostgreSQL que se utiliza para importar o exportar datos desde o hacia una tabla en la base de datos utilizando archivos de texto plano. El comando COPY es muy útil cuando se desea cargar grandes cantidades de datos en una tabla o exportar datos de una tabla para su posterior análisis o procesamiento.

Con COPY, los datos se pueden importar o exportar desde y hacia la base de datos en formatos de texto separados por comas (CSV), valores separados por tabuladores (TSV) o en un formato de texto delimitado personalizado. El comando COPY también proporciona opciones para controlar la forma en que se manejan los errores, la codificación de caracteres y la delimitación de los campos.

Es importante destacar que para utilizar el comando COPY se requiere que los archivos de origen o destino estén accesibles desde el servidor de la base de datos PostgreSQL, ya sea en el mismo servidor o en una ubicación accesible a través de una red. En resumen, COPY es un comando muy útil de PostgreSQL para importar o exportar grandes cantidades de datos de forma eficiente y sencilla utilizando archivos de texto plano.

En esta práctica, aprenderemos a AUTOMATIZAR la importación de archivos CSV en PostgreSQL utilizando el comando COPY. Para lograrlo, crearemos un script que se ejecutará diariamente mediante una tarea programada. Sin embargo, para evitar que se solicite la contraseña de usuario cada vez que se intente importar un archivo, es necesario configurar el archivo PGPASS.CONF.

POSTGRESQL - COPY

AUTOMATIZAR BACKUP

Las herramientas que nos brinda PostgreSQL para realizar copias de seguridad son "pg_dump" y "pg_dumpall", pero tienen diferentes propósitos y funcionalidades.

Por un lado,"pg_dump" es una herramienta que se utiliza para realizar copias de seguridad de una base de datos PostgreSQL específica. Esta herramienta realiza una copia de seguridad lógica de la base de datos, lo que significa que extrae los datos de la base de datos y los convierte en un formato legible por humanos. "pg_dump" también puede incluir los objetos de la base de datos, como tablas, índices, funciones y vistas. La copia de seguridad creada por "pg_dump" se puede utilizar para restaurar la base de datos en una instancia de PostgreSQL diferente o en la misma instancia después de una falla del sistema.

Por otro lado, "pg_dumpall" es una herramienta que se utiliza para realizar copias de seguridad de todas las bases de datos en un servidor PostgreSQL. "pg_dumpall" realiza una copia de seguridad lógica de todas las bases de datos en el servidor, incluyendo las bases de datos del sistema. Esta herramienta es útil para realizar copias de seguridad de todo el servidor PostgreSQL, lo que puede ser útil en situaciones en las que se desea migrar a un servidor nuevo o para realizar una copia de seguridad completa del servidor.

En resumen, "pg_dump" se utiliza para realizar copias de seguridad de bases de datos específicas, mientras que "pg_dumpall" se utiliza para realizar copias de seguridad de todas las bases de datos en un servidor PostgreSQL. Ambas herramientas son útiles para realizar copias de seguridad lógicas de una base de datos, pero tienen diferentes propósitos y funcionalidades.

En esta práctica, aprenderemos a automatizar la creación de copias de seguridad en PostgreSQL mediante el uso de la herramienta pg_dump. Crearemos un script que llame a esta herramienta y estableceremos una tarea programada para que el script se ejecute periódicamente y realice la copia de seguridad.

De esta manera, los usuarios podrán crear y mantener copias de seguridad regulares de sus bases de datos de manera eficiente y sin tener que realizar esta tarea manualmente. Además, esta práctica también explorará técnicas de administración y gestión de PostgreSQL para mejorar la seguridad y eficiencia de las operaciones de respaldo.

En definitiva, esta práctica proporcionará a los usuarios habilidades prácticas en la gestión de bases de datos y les permitirá aplicarlas en un entorno real para garantizar la integridad y disponibilidad de sus datos.

POSTGRESQL - BACKUP

EXCEPT

El comando EXCEPT en SQL se utiliza para obtener la diferencia entre dos conjuntos de resultados de consultas. En otras palabras, se puede usar el comando EXCEPT para obtener todas las filas de un conjunto de resultados que no se encuentran en otro conjunto de resultados.

El comando EXCEPT en SQL se utiliza para obtener la diferencia entre dos conjuntos de resultados de consultas. En otras palabras, se puede usar el comando EXCEPT para obtener todas las filas de un conjunto de resultados que no se encuentran en otro conjunto de resultados.

La sintaxis básica del comando EXCEPT es la siguiente:

SELECT column1, column2, ...

FROM table1 EXCEPT SELECT column1, column2, ... FROM table2;

En esta sintaxis, la primera consulta (la que se encuentra antes del comando EXCEPT) devuelve un conjunto de resultados que se restan de un segundo conjunto de resultados, que se genera a partir de la segunda consulta después del comando EXCEPT. El resultado final es un conjunto de resultados que contiene filas que están presentes en la primera consulta pero no en la segunda.

Por ejemplo, si se tienen dos tablas llamadas "table1" y "table2", y se desea obtener los registros que están en "table1" pero no en "table2", se puede usar el siguiente comando:
SELECT * FROM table1 EXCEPT SELECT * FROM table2;
Este comando devuelve todas las filas de "table1" que no están presentes en "table2".

Es importante destacar que el comando EXCEPT solo funciona con conjuntos de resultados que tengan la misma estructura (es decir, la misma cantidad y tipo de columnas). Además, el comando EXCEPT solo devuelve filas únicas, por lo que si hay filas duplicadas en la primera consulta, solo se mostrará una única vez en el resultado final.

En esta práctica aprenderemos a importar un archivo CSV a PostgreSQL utilizando tablas temporales y el comando "EXCEPT" para evitar la inserción de registros duplicados. Este método es útil cuando se tiene un archivo CSV con registros repetidos en la base de datos y se desea evitar la inserción de los mismos en la tabla de destino. Ya que la inserción de estos generaría un error al haber claves primarias repetidas.

POSTGRESQL - EXCEPT