Guarde la salida PL / pgSQL de PostgreSQL en un archivo CSV

1005

¿Cuál es la forma más sencilla de guardar la salida PL / pgSQL de una base de datos PostgreSQL en un archivo CSV?

Estoy usando PostgreSQL 8.4 con pgAdmin III y el complemento PSQL desde donde ejecuto consultas.

1
1474

¿Quiere el archivo resultante en el servidor o en el cliente?

Lado del servidor

Si desea algo fácil de reutilizar o automatizar, puede usar el comando COPY integrado de Postgresql . p.ej

Copy (Select * From foo) To '/tmp/test.csv' With CSV DELIMITER ',' HEADER;

Este enfoque se ejecuta completamente en el servidor remoto ; no puede escribir en su PC local. También debe ejecutarse como un "superusuario" de Postgres (normalmente llamado "root") porque Postgres no puede evitar que haga cosas desagradables con el sistema de archivos local de esa máquina.

Eso en realidad no significa que tenga que estar conectado como superusuario (automatizar eso sería un riesgo de seguridad de un tipo diferente), porque puede usar la SECURITY DEFINERopciónCREATE FUNCTION para crear una función que se ejecute como si fuera un superusuario .

La parte crucial es que su función está ahí para realizar verificaciones adicionales, no solo para omitir la seguridad, por lo que podría escribir una función que exporte los datos exactos que necesita, o podría escribir algo que pueda aceptar varias opciones siempre que cumplir con una estricta lista blanca. Debes comprobar dos cosas:

  1. ¿Qué archivos debería poder leer / escribir el usuario en el disco? Este podría ser un directorio en particular, por ejemplo, y el nombre de archivo podría tener que tener un prefijo o una extensión adecuados.
  2. ¿Qué tablas debería poder leer / escribir el usuario en la base de datos? Esto normalmente estaría definido por GRANTs en la base de datos, pero la función ahora se está ejecutando como superusuario, por lo que las tablas que normalmente estarían "fuera de los límites" serán completamente accesibles. Probablemente no desee permitir que alguien invoque su función y agregue filas al final de su tabla de "usuarios" ...

Escribí una publicación de blog ampliando este enfoque , incluidos algunos ejemplos de funciones que exportan (o importan) archivos y tablas que cumplen condiciones estrictas.


Lado del cliente

El otro enfoque es realizar el manejo de archivos en el lado del cliente , es decir, en su aplicación o script. El servidor de Postgres no necesita saber en qué archivo está copiando, simplemente escupe los datos y el cliente los coloca en algún lugar.

La sintaxis subyacente para esto es el COPY TO STDOUTcomando, y herramientas gráficas como pgAdmin lo envolverán en un bonito diálogo.

El psqlcliente de línea de comandos tiene un "metacomando" especial llamado \copy, que toma todas las mismas opciones que el "real" COPY, pero se ejecuta dentro del cliente:

\copy (Select * From foo) To '/tmp/test.csv' With CSV

Tenga en cuenta que no hay terminación ;, porque los metacomandos terminan con una nueva línea, a diferencia de los comandos SQL.

De los documentos :

Do not confuse COPY with the psql instruction \copy. \copy invokes COPY FROM STDIN or COPY TO STDOUT, and then fetches/stores the data in a file accessible to the psql client. Thus, file accessibility and access rights depend on the client rather than the server when \copy is used.

El lenguaje de programación de su aplicación también puede tener soporte para empujar o recuperar los datos, pero generalmente no puede usar COPY FROM STDIN/ TO STDOUTdentro de una declaración SQL estándar, porque no hay forma de conectar el flujo de entrada / salida. Manejador PostgreSQL de PHP ( no PDO) incluye muy básico pg_copy_fromy pg_copy_tofunciones que copian a / desde una matriz de PHP, que puede no ser eficiente para grandes conjuntos de datos.

13
  • 137
    Obviamente, el ejemplo anterior requiere a veces que el usuario sea un superusuario, aquí hay una versión para la gente común;) echo "COPY (SELECT * from foo) TO STDOUT with CSV HEADER" | psql -o '/tmp/test.csv' nombre_base_datosDrachenfels 17/04/12 a las 17:26
  • 10
    @Drachenfels: también \copyfunciona; allí, las rutas son relativas al cliente y no se necesita / permite el punto y coma. Ver mi edición. krlmlr 13/0213 a las 10:12
  • 3
    @IMSoP: ¿Cómo agregaría una declaración COPY a una función sql (en postgres 9.3)? ¿Entonces la consulta se guarda en un archivo .csv? jO. 12/11/2013 a las 21:24
  • 14
    Parece que \copytiene que ser de una sola línea. Por lo tanto, no obtiene la belleza de formatear el sql de la manera que desea, y simplemente poner una copia / función a su alrededor. isaaclw 17/01/2014 a las 13:49
  • 1
    @AndreSilva Como dice la respuesta, \copyes un metacomando especial en el psqlcliente de línea de comandos . No funcionará en otros clientes, como pgAdmin; probablemente tendrán sus propias herramientas, como asistentes gráficos, para realizar este trabajo. IMSoP 2 de mayo de 2018 a las 17:49
574

Hay varias soluciones:

1 psqlcomando

psql -d dbname -t -A -F"," -c "select * from users" > output.csv

Esto tiene la gran ventaja de que puede usarlo a través de SSH, como ssh [email protected] command, lo que le permite obtener

2 copycomando postgres

COPY (SELECT * from users) To '/tmp/output.csv' With CSV;

3 psql interactivo (o no)

>psql dbname
psql>\f ','
psql>\a
psql>\o '/tmp/output.csv'
psql>SELECT * from users;
psql>\q

Todos ellos se pueden usar en scripts, pero prefiero el # 1.

4 pgadmin pero eso no es programable.

16
  • 34
    En mi humilde opinión, la primera opción es propensa a errores, porque no incluye el escape adecuado de la coma en los datos exportados. Piohen 6 de mayo de 2013 a las 21:07
  • 4
    Además, psql no cita valores de celda, por lo que si CUALQUIERA de sus datos usa el delimitador, su archivo se dañará. Cerin 8/04/2014 a las 21:39
  • 7
    @Cerin -t es un sinónimo de --tuples-only (desactiva la impresión de los nombres de las columnas y los pies de página del recuento de filas de resultados, etc.) - omítelo para obtener los encabezados de las columnasic3b3rg 5/06/2014 a las 21:40
  • 22
    Acabo de probar la afirmación de escape de comas; es cierto, el método n. ° 1 no escapa de las comas en los valores. MrColes 17 de septiembre de 2014 a las 21:07
  • 1
    también use "\ pset footer" para que los recuentos de filas no se acumulen en el archivotechbrownbags 8 de mayo de 2018 a las 21:20
100

En el terminal (mientras está conectado a la base de datos) configure la salida en el archivo cvs

1) Establezca el separador de campo en ',':

\f ','

2) Establecer formato de salida sin alinear:

\a

3) Mostrar solo tuplas:

\t

4) Establecer salida:

\o '/tmp/yourOutputFile.csv'

5) Ejecute su consulta:

:select * from YOUR_TABLE

6) Salida:

\o

Luego podrá encontrar su archivo csv en esta ubicación:

cd /tmp

Cópielo usando el scpcomando o edítelo usando nano:

nano /tmp/yourOutputFile.csv
6
  • 4
    y \ o para volver a imprimir la consolametdos 6/08/12 a las 14:57
  • 2
    Esto no producirá un archivo CSV, solo registrará la salida del comando en el archivo de texto (lo que no lo hace separado por comas). Ruslan Kabalin 29/11/12 a las 16:39
  • @RuslanKabalin sí, acabo de notar eso y enmendaré la instrucción para crear una salida separada por comas (cvs)Marcin Wasiluk 30/11/12 a las 11:01
  • 5
    Mejoraría esta respuesta al señalar que la salida "csv" no se escapará correctamente y cada vez que se ejecuta un comando sql, los resultados se concatenan al archivo de salida. Danny Armstrong 6 feb 2014 a las 23:50
  • ¿Qué pasa con las nuevas líneas en los valores de campo? Los enfoques COPYo \copyse manejan correctamente (convertir a formato CSV estándar); ¿Haz esto? Wildcard 7 de enero de 2017 a las 4:19
46

Unificación de exportación CSV

Esta información no está realmente bien representada. Como esta es la segunda vez que necesito derivar esto, lo pondré aquí para recordarme a mí mismo al menos.

Realmente, la mejor manera de hacer esto (sacar CSV de postgres) es usar el COPY ... TO STDOUTcomando. Aunque no desea hacerlo de la manera que se muestra en las respuestas aquí. La forma correcta de usar el comando es:

COPY (select id, name from groups) TO STDOUT WITH CSV HEADER

¡Recuerda solo un comando!

Es ideal para usar sobre ssh:

$ ssh psqlserver.example.com 'psql -d mydb "COPY (select id, name from groups) TO STDOUT WITH CSV HEADER"' > groups.csv

Es ideal para usar dentro de la ventana acoplable sobre ssh:

$ ssh pgserver.example.com 'docker exec -tu postgres postgres psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

Incluso es genial en la máquina local:

$ psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

¿O dentro de la ventana acoplable en la máquina local ?:

docker exec -tu postgres postgres psql -d mydb -c 'COPY groups TO STDOUT WITH CSV HEADER' > groups.csv

¿O en un clúster de kubernetes, en la ventana acoplable, a través de HTTPS?:

kubectl exec -t postgres-2592991581-ws2td 'psql -d mydb -c "COPY groups TO STDOUT WITH CSV HEADER"' > groups.csv

¡Tan versátil, muchas comas!

¿Por lo menos?

Sí, lo hice, aquí están mis notas:

Las COPYses

El uso /copyejecuta de manera efectiva operaciones de archivo en cualquier sistema en el que se psqlesté ejecutando el comando, ya que el usuario que lo está ejecutando 1 . Si se conecta a un servidor remoto, es sencillo copiar archivos de datos en el sistema que se ejecuta psqlhacia / desde el servidor remoto.

COPYejecuta operaciones de archivo en el servidor como la cuenta de usuario del proceso de backend (predeterminado postgres), las rutas de archivo y los permisos se verifican y aplican en consecuencia. Si se usa, TO STDOUTse omiten las comprobaciones de permisos de archivos.

Ambas opciones requieren el movimiento de archivos posterior si psqlno se está ejecutando en el sistema donde desea que resida finalmente el CSV resultante. Este es el caso más probable, en mi experiencia, cuando se trabaja principalmente con servidores remotos.

Es más complejo configurar algo como un túnel TCP / IP a través de ssh a un sistema remoto para una salida CSV simple, pero para otros formatos de salida (binarios) puede ser mejor /copysobre una conexión de túnel, ejecutando un local psql. De manera similar, para grandes importaciones, mover el archivo fuente al servidor y usarlo COPYes probablemente la opción de mayor rendimiento.

Parámetros de PSQL

Con los parámetros psql, puede formatear la salida como CSV, pero hay desventajas como tener que recordar deshabilitar el buscapersonas y no obtener encabezados:

$ psql -P pager=off -d mydb -t -A -F',' -c 'select * from groups;'
2,Technician,Test 2,,,t,,0,,                                                                                                                                                                   
3,Truck,1,2017-10-02,,t,,0,,                                                                                                                                                                   
4,Truck,2,2017-10-02,,t,,0,,

Otras herramientas

No, solo quiero sacar CSV de mi servidor sin compilar y / o instalar una herramienta.

4
  • 1
    ¿Dónde se guardan los resultados? Mi consulta se ejecuta pero el archivo no aparece en ninguna parte de mi computadora. Esto es lo que estoy haciendo: COPIA (seleccione a, b de c donde d = '1') PARA STDOUT CON CSVHEADER> abcd.csvkRazzy R 25 abr. 18 a las 17:00
  • 1
    @kRazzyR La salida va a la salida estándar del comando psql, por lo que, en última instancia, cualquier cosa que haga con la salida estándar es donde van los datos. En mis ejemplos, uso '> file.csv' para redirigir a un archivo. Desea asegurarse de que esté fuera del comando que se envía al servidor a través del parámetro psql -c. Vea el ejemplo de 'máquina local'. joshperry 26/04/18 a las 2:02
  • 1
    Gracias por la explicación completa. El comando de copia es desesperadamente complejo con psql. Normalmente termino usando un cliente de base de datos gratuito (dbeaver community edition) para importar y exportar archivos de datos. Proporciona buenas herramientas de mapeo y formato. Su respuesta proporciona excelentes ejemplos detallados para copiar desde sistemas remotos. Rich Lysakowski PhD 28/11/19 a las 5:44
  • 1
    Ésta es una solución asombrosa. Muchas gracias. harryghgim 17 de septiembre de 2020 a las 6:50
40

Si está interesado en todas las columnas de una tabla en particular junto con los encabezados, puede usar

COPY table TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

Esto es un poquito más simple que

COPY (SELECT * FROM table) TO '/some_destdir/mycsv.csv' WITH CSV HEADER;

que, a mi leal saber y entender, son equivalentes.

1
  • 1
    Si la consulta es personalizada (IE tiene alias de columna o une tablas diferentes), el encabezado imprimirá los alias de columna tal como se muestra en la pantalla. Devy 13/11/2013 a las 21:58
30

La nueva versión, psql 12, admitirá --csv.

psql - devel

--csv

Switches to CSV (Comma-Separated Values) output mode. This is equivalent to \pset format csv.


csv_fieldsep

Specifies the field separator to be used in CSV output format. If the separator character appears in a field's value, that field is output within double quotes, following standard CSV rules. The default is a comma.

Uso:

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv -P csv_fieldsep='^'  postgres

psql -c "SELECT * FROM pg_catalog.pg_tables" --csv  postgres > output.csv
28

Tuve que usar \ COPY porque recibí el mensaje de error:

ERROR:  could not open file "/filepath/places.csv" for writing: Permission denied

Entonces usé:

\Copy (Select address, zip  From manjadata) To '/filepath/places.csv' With CSV;

y esta funcionando

1
  • 1
    También tuve el error de permiso denegado. Se corrigió enviando /tmpprimero a la carpeta. Por ejemplo: \copy (SELECT * FROM messages) TO '/tmp/messages.csv' With CSV HEADER;Somto Muotoe 1 de enero a las 2:40
21

psql puede hacer esto por usted:

[email protected]:~$ psql -d beancounter -t -A -F"," \
                -c "select date, symbol, day_close " \
                   "from stockprices where symbol like 'I%' " \
                   "and date >= '2009-10-02'"
2009-10-02,IBM,119.02
2009-10-02,IEF,92.77
2009-10-02,IEV,37.05
2009-10-02,IJH,66.18
2009-10-02,IJR,50.33
2009-10-02,ILF,42.24
2009-10-02,INTC,18.97
2009-10-02,IP,21.39
[email protected]:~$

Consulte man psqlpara obtener ayuda sobre las opciones utilizadas aquí.

1
  • 12
    Este no es un verdadero archivo CSV; observe cómo se quema si hay comas en los datos, por lo que es preferible usar el soporte de COPY incorporado. Pero esta técnica general es útil como un truco rápido para exportar desde Postgres en otros formatos delimitados además de CSV. Greg Smith 6 de octubre de 2009 a las 5:19
21

Estoy trabajando en AWS Redshift, que no admite la COPY TOfunción.

Sin embargo, mi herramienta de BI admite CSV delimitados por tabulaciones, por lo que utilicé lo siguiente:

 psql -h dblocation -p port -U user -d dbname -F $'\t' --no-align -c "SELECT * FROM TABLE" > outfile.csv
2
  • ¡Muchas gracias! He usado `psql -h dblocation -p port -U user -d dbname -F $ ',' --no-align -c" SELECT * FROM TABLE "> outfile.csv` para obtener CSV. No hay que citar los campos, pero sirve bastante bien para mis propósitosLightheaded 16/06/20 a las 18:24
  • Para su información, puede configurar .pg_service.confpara asignar un alias a los parámetros de conexión que desee psql service=default -F $'\t' ... . combinatorist 29 jul a las 19:40
12

En pgAdmin III hay una opción para exportar a un archivo desde la ventana de consulta. En el menú principal es Consulta -> Ejecutar para archivo o hay un botón que hace lo mismo (es un triángulo verde con un disquete azul en lugar del triángulo verde simple que simplemente ejecuta la consulta). Si no está ejecutando la consulta desde la ventana de consulta, haría lo que sugirió IMSoP y usaría el comando de copia.

1
  • La respuesta de IMSoP no me funcionó porque necesitaba ser un superadministrador. Esto funcionó de maravilla. ¡Gracias! Mike Neumegen 31/01/12 a las 22:08
10

Probé varias cosas, pero pocas de ellas pudieron darme el CSV deseado con los detalles del encabezado.

Esto es lo que funcionó para mí.

psql -d dbame -U username \
  -c "COPY ( SELECT * FROM TABLE ) TO STDOUT WITH CSV HEADER " > \
  OUTPUT_CSV_FILE.csv
9

Si tiene una consulta más larga y le gusta usar psql, coloque su consulta en un archivo y use el siguiente comando:

psql -d my_db_name -t -A -F";" -f input-file.sql -o output-file.csv
1
  • 3
    FWIW, tuve que usar en -F","lugar de -F";"generar un archivo CSV que se abriría correctamente en MS ExcelCFL_Jeff 31 de mayo de 2018 a las 19:44
9

Escribí una pequeña herramienta llamada psql2csvque encapsula el COPY query TO STDOUTpatrón, lo que da como resultado un CSV adecuado. Su interfaz es similar a psql.

psql2csv [OPTIONS] < QUERY
psql2csv [OPTIONS] QUERY

Se supone que la consulta es el contenido de STDIN, si está presente, o el último argumento. Todos los demás argumentos se envían a psql excepto estos:

-h, --help           show help, then exit
--encoding=ENCODING  use a different encoding than UTF8 (Excel likes LATIN1)
--no-header          do not output a header
1
  • 2
    Funciona genial. Gracias. AlexM 3 de noviembre de 2017 a las 6:52
5

Para descargar un archivo CSV con nombres de columna como ENCABEZADO, use este comando:

Copy (Select * From tableName) To '/tmp/fileName.csv' With CSV HEADER;
-2

JackDB , un cliente de base de datos en su navegador web, lo hace realmente fácil. Especialmente si estás en Heroku.

Le permite conectarse a bases de datos remotas y ejecutar consultas SQL en ellas.

                                                                                                                                                       Fuente (fuente: jackdb.com )jackdb-heroku


Una vez que su base de datos está conectada, puede ejecutar una consulta y exportar a CSV o TXT (ver abajo a la derecha).


jackdb-export

Nota: de ninguna manera estoy afiliado a JackDB. Actualmente utilizo sus servicios gratuitos y creo que es un gran producto.

0
-2

Según la solicitud de @ skeller88, estoy volviendo a publicar mi comentario como respuesta para que las personas que no lean todas las respuestas no lo pierdan ...

El problema con DataGrip es que controla su billetera. No es gratis Pruebe la edición comunitaria de DBeaver en dbeaver.io. Es una herramienta de base de datos multiplataforma de FOSS para programadores SQL, DBA y analistas que admite todas las bases de datos populares: MySQL, PostgreSQL, SQLite, Oracle, DB2, SQL Server, Sybase, MS Access, Teradata, Firebird, Hive, Presto, etc.

DBeaver Community Edition hace que sea trivial conectarse a una base de datos, emitir consultas para recuperar datos y luego descargar el conjunto de resultados para guardarlo en CSV, JSON, SQL u otros formatos de datos comunes. Es un competidor de FOSS viable para TOAD for Postgres, TOAD for SQL Server o Toad for Oracle.

No tengo ninguna afiliación con DBeaver. Me encanta el precio y la funcionalidad, pero me gustaría que abrieran más la aplicación DBeaver / Eclipse y facilitaran la adición de widgets analíticos a DBeaver / Eclipse, en lugar de requerir que los usuarios paguen la suscripción anual para crear gráficos y tablas directamente dentro la aplicación. Mis habilidades de codificación de Java están oxidadas y no tengo ganas de tomarme semanas para volver a aprender cómo construir widgets de Eclipse, solo para descubrir que DBeaver ha desactivado la capacidad de agregar widgets de terceros a DBeaver Community Edition.

¿Los usuarios de DBeaver tienen información sobre los pasos para crear widgets de análisis para agregar a la Community Edition de DBeaver?

0
-3
import json
cursor = conn.cursor()
qry = """ SELECT details FROM test_csvfile """ 
cursor.execute(qry)
rows = cursor.fetchall()

value = json.dumps(rows)

with open("/home/asha/Desktop/Income_output.json","w+") as f:
    f.write(value)
print 'Saved to File Successfully'
3
  • 3
    Explique lo que hizo al editar la respuesta, evite la respuesta de solo códigoGGO 27 feb 2018 a las 12:09
  • 3
    Gracias por este fragmento de código, que puede proporcionar una ayuda limitada a corto plazo. Una explicación adecuada mejoraría enormemente su valor a largo plazo al mostrar por qué es una buena solución al problema y lo haría más útil para futuros lectores con otras preguntas similares. Por favor, editar su respuesta a añadir un poco de explicación, incluyendo los supuestos realizados. Toby Speight 27 feb 2018 a las 12:48
  • 2
    Esto producirá un archivo json, no un archivo csv. nvoigt 27/02/18 a las 13:23