Uso de COPY para cargar datos desde el servidor

Cuando tus datos ya están en el servidor, puedes usar el comando COPY para cargarlos en tablas de PostgreSQL. Esto es especialmente útil si trabajas con grandes volúmenes de datos o tu servidor PostgreSQL está desplegado en una máquina separada.

El comando COPY ejecutado del lado del servidor es súper cómodo en proyectos reales. Primero, funciona mucho más rápido porque los archivos ya están en el servidor y no tienes que transferirlos por la red. Además, el proceso es más seguro: no necesitas copiar datos desde tu ordenador local, lo que reduce riesgos de fuga. También puedes meter la carga fácilmente en scripts automáticos del servidor o servicios en segundo plano — por ejemplo, para actualizar tablas analíticas de forma regular.

Sintaxis del comando COPY

El comando COPY es sencillo, pero hay algunos puntos clave que tienes que tener en cuenta:

COPY table_name
FROM '/path/to/file.csv'
WITH (FORMAT CSV, HEADER TRUE);

• table_name — el nombre de la tabla donde quieres cargar los datos.
• /path/to/file.csv — la ruta completa al archivo en el servidor.
• Las opciones WITH te permiten indicar el formato del archivo, si tiene cabeceras, separadores y mucho más.

Ejemplo de uso del comando COPY

Vamos a ver un ejemplo pequeño. Supón que tienes que cargar un archivo CSV con datos de estudiantes en una base de datos PostgreSQL. El archivo está en el servidor en la ruta /var/lib/postgresql/data/students.csv.

Paso 1. Tabla para los datos

Primero, asegúrate de que tienes una tabla en la base de datos donde puedas cargar los datos:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL,
    registration_date DATE
);

Esta tabla va a guardar los datos de los estudiantes: su nombre, email y fecha de registro.

Paso 2. Archivo CSV

Así podría verse el archivo /var/lib/postgresql/data/students.csv:

id,name,email,registration_date
1,Alex Lin,alex.lin@example.com,2023-09-01
2,Maria Chi,maria.chi@example.com,2023-09-02
3,Peter Ming,peter.ming@example.com,2023-09-02

Paso 3. Comando COPY

Ahora podemos usar COPY para cargar los datos del archivo en la tabla:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, HEADER TRUE);

Aquí:

• FORMAT CSV le dice a PostgreSQL que el archivo está en formato CSV.
• HEADER TRUE significa que la primera línea del archivo contiene los nombres de las columnas.

Después de ejecutar el comando, los datos del archivo llenarán la tabla students.

Comprobando el resultado

Después de cargar los datos, asegúrate de que todo fue bien:

SELECT * FROM students;

Vas a ver las filas del archivo cargadas en tu tabla:

Configuración de permisos de acceso

PostgreSQL tiene que tener acceso al archivo para usar el comando COPY. Si los permisos están mal configurados, te vas a topar con errores. Por ejemplo:

ERROR:  could not open file "/var/lib/postgresql/data/students.csv" for reading: Permission denied

Para evitar estos problemas, asegúrate de lo siguiente:

1. El archivo es legible por el usuario de PostgreSQL. Normalmente es el usuario postgres.
2. Revisa los permisos del archivo y del directorio donde está:

ls -l /var/lib/postgresql/data/students.csv

Si los permisos no son suficientes, puedes cambiarlos así:

chmod 644 /var/lib/postgresql/data/students.csv
chown postgres:postgres /var/lib/postgresql/data/students.csv

En este caso hablamos de Linux. La configuración de permisos en Windows normalmente la hace el administrador de Windows y se sale del alcance de nuestro curso.

Limitaciones del comando COPY

Trabajar con COPY tiene varias particularidades que es importante tener en cuenta:

Ruta al archivo: usa la ruta absoluta, porque PostgreSQL puede no reconocer rutas relativas.

Codificación del archivo: si el archivo CSV usa una codificación diferente a la de la base de datos (por ejemplo, Windows-1251 en vez de UTF-8), tienes que indicar la codificación explícitamente:

COPY students
FROM '/var/lib/postgresql/data/students.csv'
WITH (FORMAT CSV, ENCODING 'WIN1251', HEADER TRUE);

Errores de estructura del archivo: si la estructura del archivo no coincide con la de la tabla o los datos son incorrectos (por ejemplo, texto en un campo numérico), la carga fallará con error. ¡Revisa los datos antes de cargar!

Aplicaciones prácticas

Usar COPY para cargar datos desde el servidor tiene un montón de usos:

• Migración de datos: puedes mover grandes volúmenes de datos entre servidores o bases de datos.
• Integración con sistemas externos: muchos sistemas exportan datos en archivos CSV. COPY te permite cargar esos datos en PostgreSQL rápido.
• Preparación de informes analíticos: la carga automática de datos desde el servidor acelera el análisis de grandes volúmenes de información.

Errores típicos y cómo resolverlos

Trabajando con COPY puedes encontrarte con varios errores:

Problema: Archivo no disponible

Mensaje de error: could not open file for reading: Permission denied.

Solución: asegúrate de que el archivo es accesible para el usuario de PostgreSQL (postgres) y revisa los permisos.

Problema: Formato de archivo incorrecto

Mensaje de error: malformed CSV line.

Solución: revisa el archivo por líneas vacías, errores en los datos o caracteres raros. Asegúrate de que el separador está bien puesto.

Problema: Estructura de datos no coincide

Mensaje de error: ERROR: invalid input syntax for type integer.

Solución: asegúrate de que las columnas de la tabla coinciden con la estructura de datos del archivo. Por ejemplo, los datos numéricos deben ir en columnas numéricas y las fechas en columnas de tipo DATE.

Ahora ya tienes todo lo necesario para usar COPY de forma eficiente para cargar datos desde el servidor. Usa estos trucos en tus proyectos para ahorrar tiempo y mejorar tu productividad trabajando con bases de datos.