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

Tercera edición del libro PL/pgSQL y otros lenguajes Procedurales en PostgreSQL

Me complace comentarles que ya está publicada la tercera edición  del libro PL/pgSQL y otros lenguajes Procedurales en PostgreSQL, disponible en LULU . Orientado a DBA y programadores además de estudiantes de carreras alienada con la informática que utilicen el motor de base de datos PostgreSQL. El libro  les puede ayudar en la Programación dentro del motor de base de datos PostgreSQL en lenguajes Procedurales, funciones en SQL, PLpgSQL, PLpython, PLR. Variados Ejemplos. En esta edición esta orientada a la version 11 o superior se le agregan contenidos relacionados con la implementación de procedimientos, bloques anónimos y perfilamiento y analisis de codigo de las funciones y procedimientos, esto último un capítulo para ello solamente, se mantienen ejercicios propuestos con posibles respuestas , lo pueden encargar de aquí.

plpgsql-y-otros-lenguajes-procedurales-en-postgresql.

¿Qué cubre el libro?

Se encuentra dividido en cinco capítulos: Sigue leyendo

Accediendo a SQLITE desde PostgreSQL

Como bien he comentado en otros blogs, de los temas que más me fascinan de PostgreSQL es la posibilidad de extensión que posee y en este caso mostraremos como utilizar una extensión de los FDW, específicamente de como acceder a una base de datos SQLITE desde PostgreSQL.

Hace unos días encontré la extensión sqlite_fdw y me decidí a probarla, la cual me resulto fantástica.

Comencemos:
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

Seleccionar filas aleatorias en PostgreSQL (RANDOM y TABLESAMPLE)

En ocasiones es necesario obtener filas aleatorias de una tabla, sobre todo cuando se realizan pruebas a los datos  en cuestión. PostgreSQL permite obtener datos aleatorios, inicialmente se podía hacer con la funciones RANDOM(), pero luego de la versión 9.5 agregaron el estándar TABLESAMPLE SQL 2003 para dicha actividad, siguiendo con la política de PostgreSQL de evolución y apegándose al estándar definido. Pues veamos en que consiste:

En el ejemplo se trabajará con la tabla customers  que tienen 20000 registros

Obtener aproximadamente el 50% de los datos de la tabla, antes de la versión 9.5


dell=# SELECT count(*) FROM customers where RANDOM() <=0.5;
count
-------
10009
(1 fila)

Obtener aproximadamente el 50% de los datos de la tabla, desde versión 9.5 se utiliza TABLESAMPLE, existen dos métodos SYSTEM y BERNOULLI, los cuales puede verse sus especificaciones en la documentacion oficial al respecto, que según la misma el método SYSTEM resulta más rápido que el BERNOULLI, pero el BERNOULLI es más disperso en la muestra.

 


dell=# SELECT count(*) FROM customers TABLESAMPLE SYSTEM(50);
count
-------
9766
(1 fila)

dell=# SELECT count(*) FROM customers TABLESAMPLE BERNOULLI(50);
count
-------
10061
(1 fila) 

Como se puede observar se permite obtener datos aleatorios de una tabla en PostgreSQL, destacar además que la solución del estándar TABLESAMPLE es más rápida que la del RANDOM, pero bueno usted es libre de elegir la solución a utilizar.

Nos vemos en otra 😉