Introducción a COPY
En PostgreSQL, el comando SQL COPY se utiliza para hacer duplicados de tablas, registros y otros objetos, pero también es útil para transferir datos de un formato a otro. Por ejemplo, el comando COPY puede utilizarse para insertar datos CSV en una tabla como registros de PostgreSQL. Este artículo proporcionará varios ejemplos de PostgreSQL COPY que ilustran cómo utilizar este comando como parte de la administración de su base de datos.
Declaración COPY de PostgreSQL
La declaración SQL COPY es similar a los comandos pg_dump y pg_restore que se utilizan para hacer copias de seguridad y restaurar tablas y bases de datos. Puede utilizar el comando COPY para mover archivos dentro de una tabla PostgreSQL o para poner los datos de una tabla PostgreSQL en un archivo.
Requisitos previos
Antes de empezar a ver nuestros ejemplos de COPIADO de PostgreSQL, necesitaremos revisar algunos de los requisitos previos para este tutorial:
- Primero, necesitará tener PostgreSQL instalado y funcionando en su máquina. Si estás usando Linux, prueba el comando
service postgresql statusen una terminal para ver si PostgreSQL está instalado y en ejecución. A continuación, puedes utilizar CTRL + C para salir del proceso. En macOS o Linux, también puede canalizar el comandogrepa través depspara buscar cualquier proceso de PostgreSQL que se esté ejecutando actualmente:
|
1
|
ps aux | grep postgres
|
También puede utilizar psql -V para confirmar que la interfaz de línea de comandos psqlestá instalada y funcionando; este comando devuelve la versión de la utilidad y la información de instalación.
Debería ver una salida como la siguiente:
|
1
|
psql (PostgreSQL) 11.5
|
Conéctate a la interfaz de línea de comandos psql para PostgreSQL
Una vez que todos los requisitos previos están en su lugar, puedes intentar conectarte a la interfaz psql. Utiliza el sudo su - postgres en una terminal seguido de psql. El comando psql --help se puede utilizar para obtener más información sobre la conexión a una base de datos PostgreSQL con un usuario.
Crear una base de datos PostgreSQL
Este es el comando que usaremos en la interfaz psql para crear una base de datos PostgreSQL:
|
1
|
CREATE DATABASE somedb;
|
Ahora podemos conectarnos a nuestra base de datos mediante el comando \c para poder empezar a introducir consultas.
Crear una tabla PostgreSQL
A continuación, crearemos una tabla para utilizarla en nuestros ejemplos:
|
1
2 3 4 5 |
CREATE TABLE csv_tbl(
id SERIAL PRIMARY KEY, nombre TEXTO, apellido TEXTO ); |
Ejemplos de copia en PostgreSQL
Llegados a este punto, estamos preparados para sumergirnos en algunos ejemplos de cómo podemos utilizar el comando SQL COPY para duplicar o transferir datos.
Copiar datos de archivos de texto en Postgres
Un formato de archivo TEXT consiste en filas formadas por columnas que están separadas por un carácter delimitador; los datos se escriben o leen entonces como un archivo de texto con una línea de una tabla por fila.
|
1
2 |
COPY TABLE_NAME (col, col2)
FROM / TO ‘filename’ DELIMITER ‘ ‘; |
Podemos utilizar el comando COPY de PostgreSQL para exportar un archivo CSV a nuestra tabla de demostración con un formato de archivo TEXT:
|
1
2 |
COPY demo(firstname, lastname) TO
‘demo.txt’ DELIMITADOR ‘ ‘; |
Copiar datos binarios en Postgres
Un formato de archivo BINARY se identifica como un archivo ‘no de texto’ ya que todos los datos se almacenan o leen como formato binario. Esto puede ser más rápido que el formato de texto y csv.
Intentemos usar el comando COPY para exportar un solo registro de una tabla en un archivo binario. Esta es la sintaxis básica:
|
1
2 3 4 5 6 |
COPY (
SELECT col1, col2, col3 FROM TABLE_NAME WHERE {condition} ) TO STDOUT WITH BINARY; |
Para nuestro ejemplo, el comando quedará así:
|
1
2 3 4 5 |
COPY (
SELECT firstname, lastname FROM demo WHERE id=50 ) TO STDOUT WITH BINARY; PGCOPY |
Inserta datos CSV en PostgreSQL usando COPY
Ahora que hemos visto un par de ejemplos de exportación de datos, vamos a intentar utilizar COPY para insertar datos de filas CSV en una tabla PostgreSQL. Utilizaremos las siguientes dos filas de datos de prueba CSV para ilustrar cómo se puede utilizar COPY para insertar datos en Postgres desde un archivo CSV:
|
1
2 3 |
id, str_col, int_col, bool_col
1, «soy de un archivo CSV», 112233, true 2, «hola, Postgres», 8765, false |
Terminología de CSV
Cuando hablamos de archivos CSV, hay un par de términos clave que hay que entender:
-
DELIMITER– Un delimitador es un carácter que separa cada fila del archivo en columnas; en un archivo CSV, el delimitador es una coma. Los delimitadores no se pueden utilizar en formato binario. -
HEADER– Cuando se crea un archivo CSV, la línea de cabecera es la primera línea del archivo que contiene los nombres de las columnas.
Utilizar las palabras clave COPY y FROM para insertar datos de Postgres desde un archivo
La siguiente sentencia SQL insertará filas de datos CSV en una tabla llamada some_table:
|
1
2 3 |
COPY some_table
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
NOTA: Asegúrate de pasar la ruta absoluta del archivo como una cadena en tu cláusula FROM para evitar errores.
Utilizar COPY para recortar los espacios en blanco de los datos de la tabla PostgreSQL
Un pequeño problema con el uso de COPY para insertar datos CSV es que resultará en espacios en blanco iniciales y finales en nuestra columna de cadena. Afortunadamente, hay una solución sencilla para este problema. Intentemos insertar nuestros datos CSV de nuevo, y esta vez utilizaremos una tabla temporal para almacenar los datos. A continuación, utilizaremos la función LTRIM de SQL para eliminar los espacios en blanco de nuestra columna de cadenas antes de insertarla en nuestra tabla permanente de PostgreSQL.
NOTA: Asegúrese de truncar la tabla de prueba escribiendo TRUNCATE some_table; en psql antes de volver a insertar los datos CSV para evitar cualquier error de clave duplicada.
Crear una tabla temporal para almacenar los datos CSV
La siguiente sentencia SQL creará una tabla temporal para nuestros datos CSV:
|
1
2 |
CREATE TEMP TABLE temp_tbl AS
SELECT * FROM some_table LIMIT 0; |
Este comando SQL devolverá SELECT 0 si tiene éxito.
Ahora, vamos a probar el comando COPY de nuevo, sólo que esta vez copiaremos las filas del CSV a la tabla temporal:
|
1
2 3 |
COPY temp_tbl
FROM ‘/Users/macuser/postgres-data.csv’ DELIMITER ‘,’ CSV HEADER; |
La sentencia SQL mostrada arriba debería devolver COPY seguido del número de filas CSV que ha copiado.
Utilizar LTRIM() para eliminar los espacios en blanco de los datos de la tabla PostgreSQL
Nuestro siguiente ejemplo insertará los datos de la tabla temporal en la tabla permanente. Cuando ejecutamos esta sentencia INSERT, debemos especificar explícitamente la cabecera de cada columna y asegurarnos de pasar la columna string a la función LTRIM() para poder eliminar los espacios en blanco:
|
1
2 3 4 |
INSERT INTO some_table
(id, str_col, int_col, bool_col) SELECT id, LTRIM(str_col), int_col, bool_col FROM temp_tbl; |
Después de ejecutar este INSERT comando, los registros deberían copiarse de la tabla temporal a nuestra some_table tabla PostgreSQL. Ahora usaremos la sentencia SELECT * FROM some_table para verificar que los datos del CSV fueron insertados sin los espacios en blanco iniciales y finales.
El comando que se muestra a continuación sólo es necesario si necesita liberar memoria o espacio a corto plazo; de lo contrario, la tabla temporal se eliminará automáticamente cuando finalice la sesión de psql:
|
1
|
DROP TABLE temp_tbl;
|
Conclusión
No hay duda de que el comando SQL COPY puede utilizarse para una gran variedad de tareas de administración de bases de datos. No sólo puedes usar el comando para duplicar tablas y registros, sino que también puedes usar COPY para transferir datos de un formato a otro. En este artículo, le mostramos ejemplos de PostgreSQL COPY que ilustran cómo importar y exportar datos entre un archivo CSV y una tabla PostgreSQL. Con estos ejemplos para empezar, podrás aprovechar el poder del comando COPY en tu propia implementación de PostgreSQL.
0 comentarios