El comando COPY en el postgreSQL se utiliza para importar los datos de los archivos a la tabla de la base de datos y también para exportar las tablas de la base de datos al archivo.
Por ejemplo, si queremos importar los datos de un archivo CSV a la tabla de la base de datos, se utiliza el siguiente comando.
COPY <nombre_tabla> FROM <ruta_archivo> DELIMITADOR ‘,’ CSV;
El comando anterior supone que el archivo CSV tiene los valores de todos los campos de la tabla con delimitador como coma(,) y los campos están ordenados en el archivo igual que el de la tabla.
Si el archivo contiene los valores sólo para los campos particulares de la tabla, tenemos que dar explícitamente los nombres de los campos junto con el nombre de la tabla.
Por ejemplo, si el archivo tiene valores sólo para campo1, campo2 y campo3, pero la tabla tiene muchos campos,
COPY <nombre_tabla>(campo1, campo2, campo3) FROM <ruta_archivo> DELIMITADOR ‘ , ‘ CSV;
Si el comando COPY tuvo éxito, devolverá COPY <número de filas copiadas>
Podemos usar el HEADER en la línea de comandos que puede ser usado para ignorar la primera línea del archivo mientras se copia porque puede contener el nombre de los campos.
COPY <nombre_tabla> FROM <ruta_archivo> DELIMITADOR ‘,’ CSV HEADER;
Si el error se produce entre la operación COPY FROM, la operación se detendrá, pero los datos almacenados hasta el momento no se borrarán. Estos datos no son visibles pero ocupan el espacio en el disco. En este caso, tenemos que utilizar el comando VACUUM para limpiar los usos no deseados del disco.
Exportación a un archivo desde la tabla de la base de datos
El comando COPY TO se utiliza para copiar las tablas de la base de datos en el archivo utilizando el delimitador requerido.
COPY <nombre_tabla> A <ruta_archivo> DELIMITADOR ‘,’ CSV HEADER;
Si no quieres que los nombres de las columnas se copien en el archivo CSV, entonces puedes ignorar el HEADER.
Para exportar sólo los campos particulares, sólo puede dar los nombres de los campos con el nombre de la tabla.
COPY <nombre_tabla>(campo1, campo2, campo3) TO <ruta_archivo> DELIMITADOR ‘,’ CSV;
También podemos utilizar consultas para copiar en el fichero los registros concretos que coincidan con la condición.
COPY ( SELECT * FROM <table>WHERE id=10 ) TO <file_path> DELIMITER ‘,’ CSV;
Se recomienda dar la ruta absoluta mientras se usa COPY. El propio servidor le obliga a utilizar el nombre de la ruta absoluta mientras se utiliza COPY TO. Pero durante la lectura de archivos por COPY FROM puede utilizar tanto absoluta como relativa pathnames.
Diferencia entre COPY y \copy comando
Si usted está conectado a un servidor remoto PostgreSQL y desea copiar una tabla de registros en el archivo en la máquina local. En este caso, puede utilizar el comando \copy que es un comando incorporado de PostgreSQL. Mientras se utiliza COPY, el servidor está haciendo la operación de escritura, pero durante \copy, el psql escribe el archivo CSV y lo transfiere a la máquina local.
Para utilizar el comando \copy su máquina local debe tener privilegios suficientes. No significa los privilegios de superusuario.
pg_dump y pg_restore
El pg_dump es una herramienta para hacer una copia de seguridad de una base de datos en el archivo. Posteriormente podemos restaurarla en otra base de datos. Para respaldar la base de datos en un archivo ejecuta,
pg_dump -U <username> -h <host> -W -F t <nombre_db>><nombre_de_archivo_salida>
donde,
-W obliga a pedir una contraseña mientras se registra en la base de datos.
F especifica el formato del archivo de volcado creado, aquí t se refiere a archivo tar.
Ahora se crea el fichero de volcado para la base de datos.
También podemos restaurar el archivo en la nueva base de datos utilizando,
pg_restore -U <username> -h <host> -W -F t -d <nombre_db><nombre_archivo_dump>
Todos los parámetros son los mismos que los anteriores.