Triggers vs generate columns en PostgreSQL 12 (INSERT)

En la versión de PostgreSQL 12 salió la funcionalidad de generate columns (columnas generadas), que consiste en tener columnas generadas a partir de otras, siempre y cuando esas otras no sean generadas también. Lo cual beneficiaria directamente a los SELECT que  tienen que hacer el cálculo de una columna que dependa del valor de otras en tiempo de ejecución, el cual ya estaría  calculado con las columnas generadas.

La sintaxis para definir este tipo de columnas es la siguiente:

...GENERATED ALWAYS AS ( generation_expr ) STORED...

Ejemplo:

CREATE TABLE my_table 
(i int,
 j numeric GENERATED ALWAYS AS (i*10.5) STORED );

Sigue leyendo

Cargando datos con COPY FROM y WHERE en PostgreSQL 12

Desde la reciente versión 12 de PostgreSQL se permite utilizar la clausula WHERE en un comando COPY FROM, el cual permite cargar datos para una tabla desde el sistema de archivos en un servidor PostgreSQL filtrando los datos que no se desean, puede consultar los detalles en:

https://www.postgresql.org/docs/12/sql-copy.html

Esta actividad antes de la versión 12 solo se podía lograr cargando los datos en la tabla y luego borrando los que no se deseaban con un DELETE, por lo cual esta opción de filtrar los datos desde su carga viene siendo una mejora considerable para este proceso. Veamos un ejemplo de uso y las opciones que brinda:


CREATE TABLE copy_where (i int, t text);


--Generamos 100 datos

COPY (select i, md5(i::text) from generate_series (1,100) as i) to '/tmp/dat100.csv' csv header ;

--Generamos 10000 datos

COPY (select i, md5(i::text) from generate_series (1,10000) as i) to '/tmp/dat.csv' csv header ;

--Hacemos la carga de los 10000 datos en la tabla

pruebas12=# copy copy_where from '/tmp/dat.csv' csv header;
COPY 10000
Duración: 135,634 ms

--truncamos la tabla para aplica el WHERE para que solo cargue los datos i <= 100

pruebas12=# truncate copy_where ;

--cargamos solo 100 datos filtrando con el WHERE los datos que cumplan determinada condición, en este caso que la i <=100

copy copy_where from '/tmp/dat.csv' csv header WHERE i <= 100;
COPY 100
Duración: 21,645 ms

Sigue leyendo

Probando cstore_fdw

Hola a todos como ya saben tengo mi pasión por los FDW de PostgreSQL, tengo un post previo referente a dump_fdw, el cual es muy útil para acceder a información almacenada en archivos de backups realizados por pg_dump.

En esta ocasión les muestro el uso de cstore_fdw, el cual es desarrollado por la empresa citusdata, el mismo implementa un mecanismo de almacenamiento columnar y utiliza el formato Optimized Row Columnar (ORC), el cual puede ser ventajoso en los casos donde se realizan análisis de datos, y las principales ventajas son:

  • Compresión:  Reduce considerablemente el espacio en disco y memoria.
  • Proyección de columnas: solamente lee las columnas necesarias para la consulta.

Sigue leyendo

pgsmtp: enviando correos desde PostgreSQL

En la realización de una migración de datos desde otro gestor hacia PostgreSQL, tuvimos la necesidad de enviar correos electrónicos desde dentro del gestor, es decir desde dentro lo lógica de negocio en las funciones, PostgreSQL no brinda esta funcionalidad como nativa, pero gracias a la extensibilidad del gestor se puede desarrollar dicha funcionalidad. Por supuesto antes vimos alguna que otra solución al respecto, por ejemplo esta, la cual es en plTCL, y hace años está sin soporte,  también analizamos alguna que otra solución aislada, de las cuales decidimos hacer una nueva y compartirla con ustedes :D. Sigue leyendo

Cargar archivo JSON en PostgreSQL

Como se conoce PostgreSQL permite el almacenamiento de datos en formato JSON a través de los tipos de datos JSON y JSONB, además hemos comentado en otras publicaciones las ventajas de JSONB sobre JSON, en este caso de hoy mostraremos como cargar un archivo JSON a un campo de una tabla de PostgreSQL de una manera sencilla solo utilizado comandos de PostgreSQL , para eso utilizaremos un archivo de ejemplo del sitio  https://github.com/ozlerhakan/mongodb-json-files/tree/master/datasets en el cual seleccionamos el de students, que  tienes los datos de de estudiantes Sigue leyendo

XMLTABLE en PostgreSQL 10

La versión 10 PostgreSQL trae entre sus funcionalidades la función XMLTBABLE para procesar XML y retornar una tabla como resultado, actividad que para hacerla antes de la versión 10 podía ser algo complejo y  lenta, pero gracias a la evolución del motor ya no es tan así :D.

Según la documentación, la sintaxis que muestra es muy limpia y en realidad luego de probarla te das cuenta que es muy beneficiosa, a continuación se muestra un ejemplo de su uso con el set de datos XML que puede encontrar aquí.

Ese set de datos XML se carga es una tabla llamada “tabla” con la estructura siguiente:


CREATE TABLE tabla (dato xml);

Sigue leyendo

Casteos en PostgreSQL

PostgreSQL como otros motores de bases de datos, también soporta el casteo de tipos de datos, con el objetivo de hacer operaciones y comparaciones entre valores de distintos tipos de datos. El principio  del casteo es hacer que los  valores que se están operando sean tratado de un único o mismo tipo y así evitar errores. Para hacer casteo en PostgreSQL existen varias sintaxis las cuales se muestran a continuación:

La más común en PostgreSQL es el casteo con ‘::’ ;

SELECT '100'::numeric + 1 as resultado;
 resultado 
-----------
 101
(1 row)

Sigue leyendo

Peticiones http desde PostgreSQL

Hola a todos, pues si como se lee en el título de la entrada, hacer peticiones http desde dentro del motor de base de datos de código abierto más avanzado del mundo es posible, y todo gracias al mecanismo de extensiones que posee PostgreSQL, del cual ya he dicho que soy FAN. Que podemos hacer un un cliente http desde dentro de PostgreSQL, pues hacer peticiones WEB a sitios o servicios  restful, etc. Por ejemplo, puede que necesitemos hacer desde el motor la descarga de un sitio web para su posterior procesamiento y simplemente leer un JSON que retorna una petición restful. Para todo esto se puede utilizar la extensión pgsql-http, desarrollada por Paul Ramsey, que también tiene otras contribuciones a PostgreSQL.

Pues manos a la obra, instalaremos y probemos que tal Sigue leyendo

DIAGRAMA RELACIONAL desde PostgreSQL(Ingeniería Inversa)–parte 3: pg-table-markdown

Ya hemos tenido otras entradas de como obtener la documentación o ingeniería inversa de una bases de datos PostgreSQL en las siguientes entradas  script personalizado ,  postgresql_autodoc, en esta les traigo otra forma de hacerlo ahora con una herramienta muy sencilla que me encontré llamada  pg-table-markdown que a diferencia de las otras no me genera una imagen, sino un archivo en el formato conocido markdown, es bien sencilla como las anteriores y a continuación les muestro como se utiliza:

Primero la instalamos: Sigue leyendo

Trabajando con unidades de medida en PostgreSQL

En ocasiones necesitamos realizar algunas operaciones o  conversiones con unidades de medidas, lo cual puede ser un poco engorroso pues se tienen que hacer casteos, concatenaciones o fórmulas matemáticas, por ejemplo:

Tenemos un atributo que almacena el valor en bytes:

CREATE TABLE bytes (i int8);
INSERT INTO bytes VALUES(2000), (3000), (40000);

Necesitamos mostrar el valor en kB y además con la unidad de medidas, pues diran es simple, se castea a numeric,  se divide entre 1024, se redondea, se convierte a texto, y se concatena con ‘kB’

select round((i::numeric/1024),2)::text || 'kB' as en_Kb from bytes ;
en_kb
 ---------
 1.95kB
 2.93kB
 39.06kB
(3 rows)

Y si por casualidad se tiene almacenado el valor en texto como por ejemplo ‘1000 B’ como mil bytes, es un dolor de cabeza hacer la conversión, el casteo u operaciones matemáticas 😦

postgresql-unit al rescate

Sigue leyendo