Configuración de separadores y formatos de datos al cargar

Cuando los datos vienen de diferentes sistemas, su formato puede variar. Un archivo puede usar comas como separadores, otro — punto y coma, y en un tercero la tabulación puede ser la forma principal de separar columnas. Si configuras mal los separadores al cargar datos, puedes acabar con errores o interpretaciones incorrectas de los datos.

Además, en la vida real puedes encontrarte con situaciones donde necesitas cargar datos en formato de texto sin los típicos encabezados CSV, y también manejar valores vacíos y casos donde las líneas vacías deben interpretarse como NULL. Por eso, configurar separadores y formatos es una parte esencial cuando trabajas con cargas masivas de datos.

Separadores principales: ¿cómo usarlos?

En PostgreSQL, el comando COPY te da flexibilidad para configurar los separadores. Vamos a ver cómo funciona esto.

Configurando separadores

Por defecto, el comando COPY espera que las columnas en el archivo CSV estén separadas por comas. Pero eso no siempre es cómodo o posible: alguien puede usar punto y coma (;), barras verticales (|) o incluso tabulación (\t).

Así puedes indicar el separador usando el parámetro DELIMITER:

COPY students FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER;

Si en vez de comas se usan punto y coma:

COPY students FROM '/path/to/students.csv'
DELIMITER ';'
CSV HEADER;

Incluso puedes cargar un archivo con tabulación:

COPY students FROM '/path/to/students.tsv'
DELIMITER E'\t' 
CSV HEADER;

Aquí E'\t' indica que el separador será el carácter de tabulación.

Cargando archivos con separador no estándar

Ejemplo práctico: tienes un archivo con datos de cursos donde se usan barras verticales (|) como separadores. Los datos en el archivo se ven así:

course_id|course_name|credits
1|SQL Básico|3
2|SQL Avanzado|4
3|PostgreSQL Masterclass|5

Así puedes cargar estos datos en la tabla courses:

COPY courses(course_id, course_name, credits)
FROM '/path/to/courses.csv' 
DELIMITER '|' 
CSV HEADER;

Aquí le decimos explícitamente a PostgreSQL que el separador es la barra vertical.

Configurando formatos de datos al cargar

Los separadores son solo una parte del asunto. El formato de los datos en el archivo también es importante. Veamos las formas principales de configurar los formatos de datos.

Ignorar líneas vacías y definir NULL

A menudo en grandes volúmenes de datos hay líneas o columnas vacías que no contienen datos. PostgreSQL los interpreta como cadenas vacías si no le dices otra cosa. Para que esos valores se interpreten como NULL puedes usar el parámetro NULL AS:

Ejemplo. Supón que en tu archivo hay registros con valores vacíos:

id,first_name,last_name,email
1,John,Doe,
2,Jane,Smith,jane.smith@example.com
3,,Brown,james.brown@example.com

Cargar los datos interpretando los valores vacíos en la columna email como NULL:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '';

Como resultado, los valores vacíos en el archivo se guardarán como NULL en la tabla.

Ignorar líneas vacías

A veces el archivo puede tener líneas vacías que no necesitas cargar. PostgreSQL permite ignorar esas líneas automáticamente.

Ejemplo. Archivo con una línea vacía:

id,first_name,last_name,email
1,John,Doe,john.doe@example.com

2,Jane,Smith,jane.smith@example.com

Usa el parámetro IGNORE_BLANK_LINES:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.csv'
DELIMITER ',' 
CSV HEADER 
NULL AS '' 
IGNORE_BLANK_LINES;

Ahora las líneas vacías serán ignoradas al cargar.

Trabajando con formato de datos no estándar

A veces puedes necesitar cargar datos presentados en formato de texto en vez de CSV. Por ejemplo, las líneas en el archivo están separadas por barras verticales | y los datos no tienen línea de encabezado.

Ejemplo de archivo:

1|John|Doe|john.doe@example.com
2|Jane|Smith|jane.smith@example.com

En este caso puedes usar la siguiente consulta:

COPY students(id, first_name, last_name, email)
FROM '/path/to/students.txt' 
DELIMITER '|' 
NULL AS '' 
CSV;

Si el archivo no tiene encabezados, simplemente quita el parámetro HEADER.

Ejemplo práctico de configuración de formatos de datos

Escenario: tienes un archivo grades.tsv que contiene las notas de los estudiantes. Los datos se ven así:

student_id  course_id   grade
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Necesitas:

1. Cargar el archivo interpretando correctamente los valores vacíos como NULL.
2. Asegurarte de que los datos se cargan correctamente.

Solución:

1. Crea la tabla grades:

CREATE TABLE grades (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    grade INTEGER
);

2. Carga los datos desde el archivo:

COPY grades(student_id, course_id, grade)
FROM '/path/to/grades.tsv' 
DELIMITER E'\t' 
NULL AS '' 
CSV HEADER;

3. Comprueba los datos cargados:

SELECT * FROM grades;

Resultado esperado:

Recomendaciones y errores típicos

Error: separador incorrecto. Si no indicas el separador correcto, PostgreSQL dará error o cargará los datos mal. Por ejemplo, si el archivo usa punto y coma pero olvidas poner DELIMITER ';', el comando COPY tomará toda la línea como una sola columna.

Error: interpretación incorrecta de NULL. Si no pones el parámetro NULL AS '', los valores vacíos en el archivo pueden interpretarse como cadenas vacías, lo que suele causar errores en cálculos o filtros.

Error: formato de datos incorrecto. Configurar mal el separador o tener errores en el archivo (por ejemplo, usar tabulación en vez de espacios) puede dar un error tipo: ERROR: null value in column violates not-null constraint.