Cómo exportar datos de PostgreSQL a un archivo CSV

¿Necesita enviar datos rápidamente a un cliente o compartir un informe para su posterior análisis? Un archivo CSV es una gran opción para compartir. Echemos un vistazo a cómo puede utilizar este formato para exportar datos desde una base de datos PostgreSQL.

En este artículo, primero revisaremos qué es el formato de archivo CSV y por qué es útil al exportar su base de datos PostgreSQL. Luego exportaremos algunos datos de ejemplo de una base de datos real utilizando psql en la línea de comandos y pgAdmin, una interfaz gratuita y de código abierto para bases de datos PostgreSQL.

Antes de empezar, asegúrese de que ha configurado su base de datos PostgreSQL. Si aún no has trabajado con PostgreSQL, echa un vistazo a nuestro curso SQL para principiantes en PostgreSQL. Incluye 130 desafíos de codificación interactivos diseñados para iniciarte en tu viaje Postgres. Pero si todo lo que necesita es un repaso rápido, aquí tiene una forma de practicar sus habilidades con PostgreSQL.

¿Qué son los archivos CSV?

CSV es la abreviatura de Comma-Separated Values (valores separados por comas). Este formato de archivo le permite almacenar datos en texto plano, lo que lo hace ideal para compartir datos entre aplicaciones.

Un archivo CSV contiene filas de datos que corresponden a registros individuales; la primera fila suele contener los nombres de las columnas. Los valores de cada fila están separados por una coma. Aunque la coma es el separador más utilizado (como sugiere el nombre del formato), otros caracteres (por ejemplo, punto y coma, tabuladores o espacios) también pueden separar los valores.

Este es un ejemplo de archivo CSV. Exportaremos uno igual en las siguientes secciones:

store_id,revenue,day
1,100.42,2023-05-01
1,148.89,2023-05-02
2,238.98,2023-05-03

Como puede ver, la primera columna almacena el ID de la tienda, la segunda columna almacena los ingresos y la tercera columna almacena la fecha. Cada fila es un registro. La organización es muy parecida a la de las hojas de cálculo y las bases de datos, ¡pero todo en texto!

¿Por qué exportar una base de datos como archivo CSV?

Los archivos CSV pueden ser abiertos por casi cualquier software orientado a datos. Exportar sus datos PostgreSQL a un archivo CSV significa que usted puede compartir fácilmente la información con sus colegas, incluso si utilizan diferentes herramientas.

El formato CSV también es nativo de muchas herramientas de análisis de datos y hojas de cálculo. La exportación a través de este formato le permite transferir datos de forma rápida y sencilla para realizar análisis en profundidad, visualizaciones e informes.

Exportación de datos de PostgreSQL a CSV

Los Datos

Esta será nuestra tabla de ejemplo llamada sales. Cada fila representa cuánto ganó cada tienda en un día determinado.

sales

Exportación de datos con consultas SQL

Utilizaremos dos consultas SQL diferentes para probar distintos métodos de exportación. (Vale la pena tener cerca nuestra Hoja de Trucos PostgreSQL para aumentar la productividad cuando escriba sus propias consultas). Esta es la primera:

SELECT * FROM sales

Esta primera consulta es simple y selecciona todo lo que hay en la tabla. La idea es asegurarnos de que no perdemos datos durante la exportación.

Esta es la segunda consulta (mucho más compleja):

WITH max_revs (store_id, day, revenue, max_rev) AS (
	SELECT
		store_id,
		day,
		revenue, 
		MAX(revenue) OVER(PARTITION BY day) 
	FROM sales 
)
SELECT
	store_id,
	day,
	max_rev
FROM max_revs
WHERE revenue = max_rev;

La segunda consulta simula un informe simple; para cada día, muestra la tienda con más ingresos y los ingresos más altos. Esto es lo que se podría escribir trabajando en análisis de datos. Utilizamos una expresión de tabla común para seleccionar primero los ingresos más altos del día; la consulta externa (segunda) selecciona las tiendas que coinciden con los ingresos devueltos por la expresión de tabla común.

Si aún no está familiarizado con las CTE y las funciones de ventana, consulte la pista SQL Reporting y nuestro curso sobre Funciones de ventana en SQL. Pero por ahora, volvamos a la exportación de datos desde PostgreSQL a un archivo CSV. Una vez que tenemos los datos que queremos, necesitamos iniciar el proceso de exportación.

Exportando la Base de Datos Usando la Línea de Comandos

En primer lugar, vamos a explorar la exportación de una base de datos PostgreSQL a través de la línea de comandos. Aquí tiene dos opciones: usar el comando \copy o la sentencia COPY; explicaremos las diferencias entre ambos en un momento.

Si bien el uso de cualquiera de estos métodos es menos sencillo que el uso de pgAdmin, es probable que ya tenga todo lo que necesita para ello. Así que, comencemos.

Conectando a la Base de Datos con psql

Para ambas opciones de línea de comandos, usaremos psql. Esta es una herramienta de línea de comandos para bases de datos PostgreSQL.

Para conectarse a la base de datos, introduzca el siguiente comando:

psql -h <hostname> -p <port> -d <database name> -U <username>

Sustituya hostname por el nombre de host (o dirección) de la base de datos. El puerto se especifica en el servidor remoto junto con el nombre de la base de datos y el nombre de usuario. A continuación, se le pedirá una contraseña.

Una vez conectado a la base de datos, puede utilizar el comando \copy o el método COPY para exportar los datos. Comenzaremos con la demostración de \copy.

1. Exportación de datos mediante \copy

El comando \copy copiará directamente el resultado de su consulta local a un archivo local en su máquina. Funciona en el lado del cliente, por lo que es una gran opción si sólo tiene permisos de lectura de la base de datos.

Esta es la sintaxis:

\copy (query) to ‘filename’ with cvs [header]

Puede incluir el argumento opcional header cuando desee que la tabla generada tenga una fila extra en la parte superior que contenga todos los nombres de las columnas. Para garantizar la claridad de los resultados, utilizaré este argumento en todos los comandos futuros.

Veamos un par de ejemplos.

Ejemplo 1: Copiar toda la tabla

Código:

\copy (SELECT * FROM sales) to ‘/dbExport/sales_full_table.csv’ with csv header

Explicación: Tras ejecutar la consulta SELECT * FROM sales, el equipo copia los resultados en el archivo CSV especificado. No se envían peticiones adicionales a la base de datos.

Ejemplo 2: Copiar resultados de consulta

Código:

\copy (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) to ‘/dbExport/sales_report_query.csv’ with csv header

Explicación: Aquí se aplica el mismo principio. La base de datos ejecuta esta consulta de informe y su ordenador local copia los resultados en un archivo.

El comando \copy es fácil de usar - y en la mayoría de los casos, es tan rápido como la consulta promedio. Esto se debe a que el archivo resultante se genera localmente en lugar de enviarse desde la base de datos.

2. Uso de la sentencia COPY

A diferencia del comando \copy, la sentencia COPY se ejecuta en el lado de la base de datos. Llamamos a ésta una sentencia porque COPY es en realidad parte de un comando ejecutado por la base de datos. Esto significa que el archivo resultante se guardará en el servidor remoto, así que tenga esto en cuenta cuando elija la ruta del archivo. Guardar en un servidor remoto también significa que usted debe tener privilegios de superusuario PostgreSQL (root).

La sintaxis de la sentencia COPY es la siguiente:

COPY { table | (query) } 'filename' [ DELIMITER 'delimiter' ] [HEADER]

He omitido algunos argumentos para simplificar; si desea ver la sintaxis completa, visite la documentación de PostgreSQL

Ejemplo 1: Copiar toda la tabla

Código:

COPY sales TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explicación: Como era de esperar, esto copia toda la tabla de ventas al archivo sales_full_table.csv. Observe que hemos establecido el delimitador en coma e incluido la fila de cabecera.

Ejemplo 2: Copiar los resultados de la consulta

Código:

COPY (WITH max_revs(store_id, day, revenue, max_rev) AS (SELECT store_id, day, revenue, MAX(revenue) OVER(PARTITION BY day) FROM sales) SELECT store_id, day, revenue FROM max_revs WHERE revenue = max_rev) TO ‘/dbExport/sales_full_table.csv’ DELIMITER ‘,’ CVS HEADER

Explicación: Como sugiere el subtítulo, esto exporta los resultados de la consulta al archivo sales_full_table.csv. He aquí un ejemplo de los datos en forma de tabla:

Como puede ver, el resultado es el mismo que el comando \copy; la única diferencia es el lugar donde se almacena el archivo resultante.

Exportación de datos con pgAdmin

Veamos ahora la segunda opción, que utiliza una interfaz gráfica de usuario (GUI). Puede que ya esté familiarizado con pgAdmin; es un programa de gestión de bases de datos PostgreSQL gratuito y de código abierto. Esta es una forma simple y directa de exportar datos desde una base de datos Postgres.

Conectarse a la base de datos

Abra pgAdmin y haga clic en Añadir nuevo servidor.

En la página General, elija un nombre para la conexión.

En la página Conexión , introduzca el nombre de host ("localhost" si está ejecutando la base de datos en su propio ordenador), el nombre de la base de datos, el nombre de usuario y la contraseña.

Por último, haga clic en Guardar. Si la conexión se ha establecido correctamente, la página del panel de control mostrará gráficos de la actividad de la base de datos.

Exportar los resultados de la consulta

Primero abriremos la herramienta de consulta haciendo clic en el icono de la base de datos (tres discos) en la barra de herramientas superior o utilizando Alt+Shift+Q. Ahora puede consultar la base de datos escribiendo consultas en el cuadro de texto y ejecutándolas con el botón de reproducción de la parte superior o con la tecla F5.

Una vez ejecutada una consulta, su resultado se mostrará en la ventana inferior. Para guardar el resultado en un archivo CSV, pulse el botón de descarga situado justo encima de los datos resultantes.

Como puede ver, los resultados son los mismos que con las herramientas de línea de comandos. La principal diferencia es que pgAdmin hace que cambiar entre diseñar consultas y guardar los resultados sea mucho más fácil.

Más información sobre la exportación de datos con archivos CSV

¡Exportar bases de datos a archivos CSV es un placer! Ahora que sabes cómo exportar todos los datos que necesitas, puede que tengas otra pregunta: ¿Cómo puedo importar datos a una base de datos Postgres? Lea este breve artículo sobre cómo importar datos a PostgreSQL usando pgAdmin para averiguarlo.

Y si está buscando expandir su conocimiento con PostgreSQL, revise nuestro tema completo De la A a la Z con PostgreSQL, que incluye más de 1,000 ejercicios interactivos. ¡Feliz aprendizaje!