Etiqueta: PostgreSQL

Exportar datos de PostgreSQL a un fichero CSV

en por Donnie Rocken Informática, SQLDeja un comentario

En algún momento ya habíamos hablado por aquí del formato CSV (comma separated values), un estándar para compartir información mecánicamente legible que, a pesar de algunas limitaciones con la codificación de caracteres, es bastante popular pues, por ejemplo, nos permite pasar datos de un sistema gestor de bases de datos a una hoja de cálculo o viceversa.

El sistema gestor de bases de datos PostgreSQL nos permite exportar datos a CSV de forma muy sencilla, y además tenemos dos posibilidades: Exportar una tabla entera o exportar el resultado de una consulta.

El comando básicamente sería COPY loquesea TO ficherodesalida DELIMITER ‘caracterdelimitador’ CSV HEADER; siendo el caracter delimitador generalmente una coma, pero puedes cambiarlo por otro si lo necesitas para tu exportación (puede darse el caso de que los datos que exportes tengan textos con coma, por lo que igual te interesa un caracter más exótico). El loquesea del ejemplo sería o el nombre de una tabla o una consulta, mientras que ficherodesalida sería la ruta donde queremos que se cree nuestro CSV.

Veamos dos ejemplos. Imaginemos que queremos expotar todos los datos de una tabla que se llama Pacientes:

COPY Pacientes TO 'Users/Donato/Documents/Pacientes.csv' DELIMITER ',' CSV HEADER;

Esa instrucción generaría un fichero con el nombre que le hemos definido en la ruta que le hemos ordenado, conteniendo todos los valores de la tabla pacientes separados por comas. Ahora imaginemos que solo queremos los nombres y apellidos de los pacientes del año 2021 (tenemos un campo con el año en el que se dieron de alta), podríamos exportarlos usando una consulta:

COPY (Select Nombre, Apellidos from Pacientes where Alta = 2021) TO 'Users/Donato/Documents/Pacientes.csv' DELIMITER ',' CSV HEADER;

De esta forma exportaríamos solo esos datos que nos interesan, filtrados a través de esa consulta. Ahora podríamos abrir esos datos en Excel, Googl Sheet o LibreOffice Math, por ejemplo.

PostgreSQL: Instrucciones básicas de gestión de tablas

en por Donnie Rocken Informática, Lenguajes de programaciónDeja un comentario

Una entrada básica de SQL ¿Cuáles son las instrucciones básicas de gestión de tablas en el SGBD PostgreSQL? Veámoslas:

Crear tabla (para el ejemplo crearemos una tabla de de tres columnas, con un id autonumérico, una columna de texto que no admite nulos y una columna para números decimales con un valor por defecto de 0):

CREATE TABLE t (
  id SERIAL PRIMARY KEY,
  columna1 VARCHAR NOT NULL,
  columna2 NUMERIC(10,2) DEFAULT 0
);

Para borrar una tabla tenemos dos opciones, la primera de ella será hacerlo con el parámetro RESTRICT o sin ningún parámetro, pues esta es la opción por defecto. Si hay objetos que dependan de esa tabla, como vistas o claves externas, no borrará la tabla y nos devolverá un mensaje de error informándonos:

DROP TABLE laTablaQueSea RESTRICT;

La otra opción es borrar en casacada, con el parámetro CASCADE. En este caso borra la tabla y todos los objetos que dependan de la misma.

DROP TABLE laTablaQueSea CASCADE

Si lo que queremos es añadir una columna entonces tenemos que usar ALTER TABLE seguida de la instrucción ADD COLUMN especificando el nombre de columna, el tipo de datos y las restricciones que se aplicarán sobre la misma.

ALTER TABLE laTablaQueSea
ADD COLUMN nombreDeColumna tipoDeDatos constraint;

Si queremos añadir varias columnas solo tenemos que repetir la instrucción ADD COLUMN separada por comas:

ALTER TABLE laTablaQueSea
ADD COLUMN nombreDeColumna tipoDeDatos constraint,
ADD COLUMN nombreDeColumna2 tipoDeDatos constraint,
ADD COLUMN nombreDeColumna3 tipoDeDatos constraint;

En cambio si queremos borrar una columna tenemos que usar DROP COLUMN seguido del nombre de la columna:

ALTER TABLE laTablaQueSea DROP COLUMN nombreDeColumna;

Para deshabilitar los triggers vinculados a una tabla tenemos que usar también ALTER TABLE:

ALTER TABLE laTablaQueSea DISABLE TRIGGER ALL;

Para volver a habilitar estos triggers sería lo mismo pero con el parámetro ENABLE:

ALTER TABLE laTablaQueSea ENABLE TRIGGER ALL;

Para añadir alguna restricción a la tabla también usamos ALTER TABLE, en ese caso seguido de ADD y de la restricción que sea.

ALTER TABLE laTablaQueSea ADD laRestriccionQueQuieras;

Para borrar una restricción sobre una tabla utilizamos ALTER TABLE, pero ahora con DROP:

ALTER TABLE laTablaQueSea DROP laRestriccionQueQuieras;

Para renombrar una tabla tenemos que usar RENAME después del ALTER TABLE:

ALTER TABLE laTablaQueSea RENAME TO otroNombreDeTabla;

Y para renombrar una columna es similar:

ALTER TABLE laTablaQueSea RENAME columna1 TO columna2 ;

Finalmente si queremos borrar todos los datos de una tabla sin borrar la tabla, nuestra opción es la instrucción TRUNCATE TABLE:

TRUNCATE TABLE laTablaQueSea CASCADE;

Common Table Expression (CTE): la sentencia WITH en PostgreSQL.

en por Donnie Rocken Informática, SQLDeja un comentario

Hace tiempo escribí una entrada sobre el uso de CTE en SQL-Server de Microsoft y no hace mucho en dicha entrada me preguntaron si podría usarse también una CTE en PostgreSQL. La respuesta es que sí, existe este recurso también en ese sistema gestor de bases de datos.

Sintaxis básica de una CTE

Recuerda, una CTE es un artefacto que nos permite mantener en memoria el resultado de una consulta para poder utilizarlo como si se tratase de una tabla o vista más.

En PostgreSQL usaría la misma sintaxis que en SQL-Server: tras la instrucción WITH definimos el nombre de la CTE y podemos opcionalmente agregar también los nombres que queramos dar a las columnas de la tabla que devolverá como resultado (si no lo hacemos entonces se usarán por defecto los nombres de las columnas que traigamos en la consulta). Dentro del cuerpo de la instrucción WITH definimos la consulta que queramos usar en la CTE. Finalmente ya podremos usar nuestra CTE dentro de una consulta. En fin, que lo básico sería tal que así:

WITH cte_name (column_list) AS (
    Select * from TablaQueNecesitemos
)

El mismo ejemplo que usé en la entrada sobre SQL-Server nos vale para aquí, una CTE sobre dos tablas (vendedores y ventas) para ver el total de ventas de cada uno ese año:

WITH Ventas_CTE (IdVendedor, NombreVendedor, TotalVendido)  
AS  
(  
    SELECT 
      v.Id as IdVendedor, 
      v.Nombre as NombreVendedor, 
      SUM(vt.Importe) AS TotalVentas 
    FROM Vendedores v inner join ventas vt on v.Id = vt.IdVendedor
    WHERE YEAR(vt.Fecha)=2018
)

Después podríamos usar esa CTE como una tabla más en cualquier consulta de SELECT, INSERT,UPDATE o DELETE. Recordad que como en SQL-Server no podremos usar cláusulas coo ORDER BY o FOR BROWSE, algo que tampoco tendría mucho sentido por otra parte.

¿Por qué usar una CTE?

La misma pregunta que nos hacíamos en el primer artículo. Hay motivos varios para usar una CTE con PostrgreSQL:

  • Hacer más legible y más entendible una consulta compleja, permitiéndonos prescindir de variables de tabla y subconsultas, consiguiendo un código más ordenado y limpio sin penalizar el rendimiento.
  • La posibilidad de crear consultas recursivas (un tema del que prometí escribir hace dos años y se me pasó)
  • La posibilidad de usar funciones de agregado o funciones ventana y poder operar con ellas después.

SQL: Formas Normales

en por Donnie Rocken Informática, SQLDeja un comentario

Recuperados ya del fin de semana de rock and roll vamos a volver al código con algo de SQL teórico: la normalización de datos. El diseño lógico de las tablas y sus relaciones es fundamental para la optimización de una base de datos. Existen cinco reglas de normalización que debemos cumplir para aseverar que nuestra base de datos SQL está normalizada, aunque es cierto que hay quien considera que la cuarta y la quinta son rizar el rizo y que con cumplir la tres primeras generalmente ya tenemos una base de datos normalizada. Estas son las cinco formas normales:

  1. Primera Forma Normal: La primera forma requiere la eliminación de todas las columnas duplicadas de una tabla, la separación en otras tablas de esos datos que se duplicarían y la identificación de cada tabla con una atributo de clave primaria. Por ejemplo, si tenemos una tabla donde registramos ventas no guardamos el nombre y el precio del producto vendido en ella varias veces, sino que tendremos una tabla de productos con esos datos separado de la tabla de ventas, y ambas tendrían claves primarias que identifican a cada fila.
  2. Segunda forma Normal: Implica que se cumpla lo dicho en la primera forma normal y que, además, se creen relaciones entre tablas a través de claves externas. Es decir, la tabla Ventas del ejemplo anterior incluye como Clave Externa un valor único que lo relaciona con la tabla Productos, generalmente su clave primaria.
  3. Tercera Forma Normal: Esta norma implica que se cumplan las dos anteriores y que, además, todas las columnas de un registro deben hacer referencia únicamente a la clave primaria, y además elimina todos los atributos derivados. Volvemos al ejemplo: la tabla Productos nos dará el nombre del producto, su precio, en qué almacén se guarda y su fecha de caducidad. No tendremos, por ejemplo, un registro para decir en qué piso del almacén se guarda, ya que ese dato sería de la tabla Almacén. Tampoco tendríamos una columna con los días que faltan hasta que caduque, ya que ese sería un atributo derivado que podemos calcular con la fecha de caducidad.
  4. Cuarta Forma Normal: Agrega un requisito adicional, que es la eliminación de cualquier dependencia multivaluada en las relaciones. Una tabla con una dependencia multivaluada es una donde la existencia de dos o más relaciones independientes muchos a muchos causa redundancia.
  5. Quinta Forma Normal: Rizando el rizo, vendría a decir que sólo se podrían realizar relaciones entre tablas utilizando claves candidatas, con la idea de reducir la redundancia de datos entre múltiples tablas.

A la hora de normalizar bases de datos hay una frase en inglés que lo resume todo: «The key, the whole key, and nothing but the key.» En la propia web de Microsoft la primera recomendación que dan para mejorar el rendimiento de SQL-Server y tener un diseño eficiente de la base de datos es usar un índice autonumérico como clave primaria de cada tabla, identificando así de forma unívoca cada registro y facilitando la relación entre ellos.

PostgreSQL: Obtener todas las fechas que existen entre dos fechas dadas

en por Donnie Rocken Informática, SQL1 comentario

El otro día veíamos cómo crear en SQL-Server una tabla con todas las fechas entre dos fechas dadas. Hoy vamos a ver cómo hacer un simple select valiéndonos de la función generate_series() para obtener todas las fechas comprendidas entre dos fechas dadas con PostgreSQL

select i::date from generate_series('2017-06-01', 
  '2017-07-31', '1 day'::interval) i

PostgreSQL: Consulta para saber en qué tabla está una columna

en por Donnie Rocken Informática, SQLDeja un comentario

Lo hicimos en Oracle, en SQL-Server y en MySQL. No podíamos dejarnos PostgreSQL ¿cómo podemos saber en qué tabla está una columna sabiendo sólo el nombre de dicha columna?

Pues con una consulta sobre la vista columns, que contiene información sobre las columnas de todas las tablas y vistas. Veamos cómo sabríamos en qué tabla está la columna «NombrePaciente» (por poner un ejemplo práctico):

Select table_name
from columns
where column_name like 'NombrePaciente'

En vuestro caso cambiáis NombrePaciente por el nombre de columna que corresponda.

PostgeSQL: Calcular una edad a partir de una fecha.

en por Donnie Rocken Informática, SQLDeja un comentario

No hace mucho veíamos cómo calcular una edad en SQL-Server. Vamos ahora con otro SGBD ¿Cómo lo hago en Postgres? Pues con mucha menos dificultad, porque este gestor incluye ya una función integrada para el cálculo de edades, presente al menos desde la versión 9 (no se si antes) llamada age().

La función puede ser llamada con uno o con dos argumentos, siempre del tipo timestamp, y devuelve el interváluo entre ambos. Para el ejemplo imaginemos una tabla llamada clientes donde tenemos un campo FechaNac con la fecha de nacimiento guardada.

/*Con el primer ejemplo
pasando sólo una fecha
cogería la edad a día de hoy
de alguien nacido en esa fecha*/
Select age(timestamp FechaNac) from clientes

/*Con el segundo ejemplo
pasando dos fechas
cogería la edad que una
persona nacida en la fecha 2
tenía en la fecha 1*/

Select age(timestamp '2014-01-01',timestamp FechaNac) from clientes

Script para gestión de backups de PostgreSQL en FreeBSD

en por Donnie Rocken Informática, SQLDeja un comentario

No recuerdo si había comentado que estaba en plena migración tecnológica laboral, trabajando con el SGBD libre PostgreSQL y el sistema operativo (libre, of course) FreeBSD. Y el primero de los trasteos era simple ¿cómo automatizo los backups? Ya habíamos hablado de este tipo de cuitas alguna vez por aquí, así que vamos a ver cómo con un script, y haciendo uso de Cron, podemos lograrlo:

#!/bin/sh
# pgsql backups --datosde conexion
db_user="tu_usuario"
db_name="tu_base_de_datos"
db_dumpdir="/var/backups/carpeta"
date_format="`date +%Y-%m-%d.%H:%M`"

#ARRIBA CAMBIA LOS EJEMPLOS POR LOS DATOS QUE CORRESPONDAN. 
 
# BORRAMOS LOS BACKUPS MÁS VIEJOS
# PARA NO SATURAR
# EN ESTE CASO, LOS QUE TENGAN MÁS DE
# 4 SEMANAS

expire_time="+4w"
 
# Begin Script #
/usr/local/bin/pg_dump -U $db_user $db_name -f "$db_dumpdir/$db_name-$date_format.pgsql"
/usr/bin/find $db_dumpdir -d 1 -Btime $expire_time -delete

Con este ya tenemos un script que nos genera un backup de la base de datos en nuestra carpeta de copias de seguridad y además borra los que tengan más de cuatro semanas, para que no saturemos el disco duro. Y ahora, como dijimos antes, necesitamos Crontab para automatizar la copia. Asumamos que guardamos el script en la dirección /usr/local/scripts/pg_backup.sh, porque somos gente ordenadita (al menos a la hora de organizar discos duros). En nuestro caso ejecutaremos el script cada 6 horas:

# crontab -e
* /6 * * * /usr/local/scripts/pg_backup.sh > /dev/null 2>&1

Y con esto ya tenemos la cuestión de automatizar las copias de seguridad solventada.

Consulta SQL para conocer el número de campos de una tabla

en por Donnie Rocken SQLDeja un comentario

Por avatares del destino hoy estaba modificando una página en ASP clásico y necesitaba saber el número de campos que tenía una tabla. Basta con una consulta para tener estos datos:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
WHERE Table_Name = 'nombre_de_la_tabla'
GROUP BY Table_Name;

Si queréis saber el de todas las tablas de la base de datos podéis hacer lo siguiente:

SELECT Table_Name, COUNT(*) As NumeroCampos
FROM Information_Schema.Columns
GROUP BY Table_Name
ORDER BY Table_Name

Evitar SQL-injection en PHP

en por Donnie Rocken Informática, Lenguajes de programación, SQL4 comentarios

Los ataques SQL-Injection son unos de los más habituales en el mundo web. Aquí vamos a ver una serie de consejos para evitar estos agujeros en la seguridad:

Lo primero es seguir una serie de consejos a nivel de administración, como limitar los permisos del usuario a nivel de base de datos. Por ejemplo, en la mayoría de aplicaciones web lo habitual es que no tenga que utilizarse DROP, por lo que por seguridad sería mejor no permitir ya al usuario hacerlo. También sería interesante no dar información extra al atacante evitando sacar en pantalla los errores de la base de datos. En ese caso lo ideal es capturar el error haciendo uso de exception. Tampoco hay que confiarlo todo a las validaciones por javascript, porque el atacante puede saltárselas desactivándolo desde el navegador, por lo que mejor hacer las validaciones del lado del servidor. Y finalmente escapar las comillas con msqli_real_scape_string (en caso de que usemos MySQL), pg_scape_string (en caso de PostgreSQL) o addslashes (por si utilizamos otro SGBD). También podemos utilizar htmlentities para convertir los textos en entidades html, como un plus a la seguridad. Abajo un simple ejemplo:


<?php

try{
  $query = sprintf("SELECT * FROM users WHERE id=%d", mysqli_real_escape_string($id));
  $query = htmlentities($query);
  mysqli_query($query);
}catch(Exception $e){
    echo('Lo sentimos, ha habido un error en la conexión');
}
?>

Como medida extra se podrían utilizar expresiones regulares para evitar la inserción de ciertas palabras (SELECT, DROP, UNION…), si bien puede resultar poco práctico, sobre todo si tu software está destinado al mercado británico.

Pero si todo esto te parece lioso, hay una alternativa: la clase PDO. Dicha clase nos facilitará mucho la vida a la hora de trabajar con bases de datos, ya que nos permite abstraernos del SGDB que estemos utilizando. Si por ejemplo, en una página donde no utilices PDO o algo similar sino el mysql_connect simple, decides migrar tu web de MySQL a PostgreSQL tendrías que cambiar todos los métodos del conector de MySQL por los métodos de PostgreSQL. Con PDO bastaría con que cambiaras una sola línea de código, concretamente la de la creación del objeto PDO, y el resto de la aplicación seguiría funcionando. PDO además te permitirá usar consultas parametrizadas (como los Prepared Statements de java) o realizar transacciones. En fin, en el enlace de arriba tenéis todo el manual de PDO para estudiarlo si queréis. Ahora vamos con un simple ejemplo de Prepared Statement, para que veáis lo sencillo que es (dando por sentado para el ejemplo que ya hemos creado el objeto PDO, tal cual está explicado en el manual del enlace).


<?php
  $prepared_statement = $pdo->prepare("SELECT name FROM usuarios WHERE id = :id");/*preparamos la consulta*/
  $prepared_statement->bindParam(':id', $_GET['id'], PDO::PARAM_INT); /*Le pasamos el parámetro, asociado al parámetro de la consulta y definiendo su tipo (si no, por defecto lo trata como string)*/
  $prepared_statement->execute(); /*ejecutamos*/
  $prepared_statement = $statement->fetch(); /*recogemos los resultados, como un array. Se pueden utilizar parámetros para especificar otro tipo de respuesta, como por ejemplo PDO::FETCH_ASSOC para obtener un array asociativo*/
?>

Y con estos breves consejos lograrás que tu página sea más segura. El consejo: utiliza PDO, por ahorrarte comeduras de cabeza, por seguridad y por portabilidad de tu código.