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


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

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


686
2017-10-04 22:58


origen


Respuestas:


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

Lado del servidor

Si desea algo fácil de reutilizar o automatizar, puede usar Postgresql's built DUPDO mando. p.ej.

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

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 "raíz") 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 otro tipo), porque puede usar el SECURITY DEFINER opción de CREATE FUNCTION para hacer una función que funciona como si fueras un superusuario.

La parte crucial es que su función está ahí para realizar comprobaciones adicionales, no solo pasar por alto la seguridad, para que pueda 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 lista blanca estricta. Debe verificar dos cosas:

  1. Cual archivos ¿se le debe permitir al usuario leer / escribir en el disco? Esto podría ser un directorio particular, por ejemplo, y el nombre del archivo podría tener un prefijo o extensión adecuada.
  2. Cual mesas ¿debería el usuario leer / escribir en la base de datos? Esto normalmente se definiría por GRANTs en la base de datos, pero la función ahora se ejecuta como un superusuario, por lo que las tablas que normalmente estarían "fuera de límites" serán totalmente accesibles. Probablemente no quiera que alguien invoque su función y agregue filas al final de su tabla de "usuarios" ...

He escrito una publicación de blog que se expande en 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 hacer 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 lo coloca en algún lugar.

La sintaxis subyacente para esto es la COPY TO STDOUT comando, y las herramientas gráficas como pgAdmin lo envolverán en un bonito cuadro de diálogo.

los psql cliente de línea de comandos tiene un "meta-comando" 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 en nueva línea, a diferencia de los comandos SQL.

De los documentos:

No confunda COPY con la instrucción psql \ copy. \ copy invoca COPY FROM STDIN o COPY TO STDOUT, y luego recupera / almacena los datos en un archivo accesible para el cliente psql. Por lo tanto, los derechos de acceso y acceso a los archivos dependen del cliente y no del servidor cuando se usa \ copy.

El lenguaje de programación de su aplicación mayo también tiene soporte para empujar o recuperar los datos, pero generalmente no se puede usar COPY FROM STDIN/TO STDOUT dentro de una declaración SQL estándar, porque no hay forma de conectar la corriente de entrada / salida. El controlador PostgreSQL de PHP (no PDO) incluye muy básico pg_copy_from y pg_copy_to funciones que copian a / desde una matriz PHP, que puede no ser eficiente para grandes conjuntos de datos.


1031
2017-10-04 23:18



Hay varias soluciones:

1 psql mando

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

Esto tiene la gran ventaja de que puedes usarlo a través de SSH, como ssh postgres@host command - lo que le permite obtener

2 postgres copy mando

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 pueden usarse en scripts, pero prefiero el # 1.

4 pgadmin pero eso no es scriptable.


410
2017-08-08 17:56



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

1) Establecer el separador de campo para ',':

\f ','

2) Establecer formato de salida desalineado:

\a

3) Mostrar solo tuplas:

\t

4) Establecer salida:

\o '/tmp/yourOutputFile.csv'

5) Ejecuta tu consulta:

:select * from YOUR_TABLE

6) Salida:

\o

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

cd /tmp

Cópialo usando el scp comando o editar usando nano:

nano /tmp/yourOutputFile.csv

75
2018-06-11 11:18



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

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

Esto es un poco más simple que

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

que, a lo mejor de mi conocimiento, son equivalentes.


32
2018-01-11 20:34



Tuve que usar el \ 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 está funcionando


20
2018-06-28 21:41



psql puede hacer esto por usted:

edd@ron:~$ 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
edd@ron:~$

Ver man psql para obtener ayuda sobre las opciones utilizadas aquí.


14
2017-10-04 23:12



En pgAdmin III hay una opción para exportar a un archivo desde la ventana de consulta. En el menú principal es Query -> Execute to file 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 solo ejecuta la consulta). Si no está ejecutando la consulta desde la ventana de consulta, entonces haré lo que sugirió IMSoP y usaré el comando de copia.


11
2017-11-04 16:58