Estoy generando una lista de comandos SQL para exportar algunos datos que finalmente ejecuto usando psql -f. Todas las consultas obtienen el mismo subconjunto de datos, así que pensé en factorizar las calificaciones y poner una lista de ID de usuario elegibles en tablas temporales como
create temporary table tmp_export_users as (select id from users where ...) y luego volver a consultar eso en mi \ comandos de copia como
\copy (select ... from table where user_id in (select id from tmp_export_users)) TO "filename.csv" WITH CSV HEADER Todos están en el mismo archivo, uno por línea, y ejecutándolos, si obtengo el error de que los comandos de copia pueden » Como veo la tabla temporal, supongo que el comando de copia del cliente no debe usar la misma sesión de postgres que psql.
¿Es eso correcto? ¿Hay alguna forma de cambiar ese comportamiento?
Responder
\copy puede usar una tabla temporal.
Primero probé y confirmé esto con la versión 9.0 en la línea de comando.
Luego creé un archivo con el metacomando SQL y psql \copy usando múltiples tablas temporales. Eso también funcionó para mí.
CREATE TEMP TABLE tmp as SELECT * FROM tbl; \copy (SELECT * FROM tmp JOIN tbl USING (id)) TO "/var/lib/postgres/test1.csv"; Llamar:
psql -p5432 mydb -f test.sql Tenga en cuenta la terminación punto y coma, que es opcional al final de un archivo (terminado implícitamente), pero necesario después de cualquier otra instrucción SQL y también después de la última si se ejecuta en psql de forma interactiva.
Normalmente , los metacomandos psql no se pueden mezclar con SQL en la misma línea en un archivo ejecutado por psql -f. Cito el manual en psql :
El análisis de argumentos se detiene al final de la línea, o cuando se encuentra otra barra invertida sin comillas. Una barra invertida sin comillas se toma como el comienzo de un nuevo metacomando. La secuencia especial
\\(dos barras invertidas) marca el final de los argumentos y continúa analizando los comandos SQL, si los hay. De esa manera, los comandos SQL y psql se pueden mezclar libremente en una línea. Pero en cualquier caso, los argumentos de un metacomando no pueden continuar más allá del final de la línea.
Se aplican reglas diferentes después de \copy, sin embargo. Básicamente, psql vuelve al modo SQL automáticamente después de \copy Ver:
Pero escribió que tenía todos los comandos en líneas separadas. Así que esa no puede ser la explicación en su caso.
Aparte de eso, ¿ha considerado usar COPY (el comando SQL ) en lugar de \copy ( el psql metacomando )?
Por supuesto, el archivo de destino tendría que ser local al servidor no al cliente en este caso. Y se aplican diferentes privilegios de archivo. El manual :
Archivos nombrados en un
COPYson leídos o escritos directamente por el servidor, no por la aplicación cliente. Por lo tanto, deben residir o ser accesibles para la máquina del servidor de la base de datos, no el cliente. Deben ser accesibles y legibles o escribibles por el usuario de PostgreSQL (el ID de usuario con el que se ejecuta el servidor), no el cliente.
Comentarios
- la copia se ejecuta como el usuario de postgres, \ copy envuelve la copia para escribir en std y ser redirigido al archivo al que lo envía. También puede llamar a psql, usar \ o para enviar la salida a un archivo y luego ejecutar una copia en stdout para obtener un efecto similar.
- Para estar seguro de que ejecuté la prueba en mi respuesta con un superusuario (postgres ) y un usuario ficticio. Ambos funcionan para mí. Los mismos resultados en v8.4.
- Sí, si el usuario de postgres unix puede acceder o no a cosas como / tmp depende de cosas como si SELinux está instalado o no y si lo deja fuera de su caja. \ Copy o copy to stdout son definitivamente las dos formas más confiables de usar copy.
- Gracias por las respuestas a todos. Parece que me olvidé de terminar la línea que creó una tabla temporal con un punto y coma, por lo que no ' se creó. Trabajando como se esperaba ahora