Configurazione dei separatori e dei formati dati durante il caricamento

Quando i dati arrivano da sistemi diversi, il loro formato può variare. Un file può usare le virgole come separatori, un altro i punti e virgola, e in un terzo la tabulazione può essere il modo principale per separare le colonne. Se non imposti correttamente i separatori durante il caricamento dei dati, rischi errori o una cattiva interpretazione dei dati.

In più, nella vita reale potresti trovarti in situazioni dove devi caricare dati in formato testo senza le classiche intestazioni CSV, oppure gestire valori vuoti e prevedere casi in cui le righe vuote devono essere interpretate come NULL. Quindi la configurazione dei separatori e dei formati è una parte fondamentale quando lavori con il caricamento massivo dei dati.

Separatori principali: come gestirli?

In PostgreSQL il comando COPY ti dà molta flessibilità nella configurazione dei separatori. Vediamo come funziona.

Impostare i separatori

Di default il comando COPY si aspetta che le colonne in un file CSV siano separate da virgole. Ma non sempre è comodo o possibile: qualcuno potrebbe usare punti e virgola (;), barre verticali (|) o anche la tabulazione (\t).

Ecco come puoi specificare il separatore usando il parametro DELIMITER:

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

Se invece delle virgole vengono usati i punti e virgola:

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

Puoi anche caricare un file con la tabulazione:

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

Qui E'\t' indica che il separatore sarà il carattere di tabulazione.

Caricamento di file con separatore non standard

Esempio pratico: hai un file con dati sui corsi dove vengono usate barre verticali (|) come separatori. I dati nel file sono così:

id_corso|nome_corso|crediti
1|SQL Base|3
2|SQL Avanzato|4
3|PostgreSQL Masterclass|5

Ecco come puoi caricare questi dati nella tabella courses:

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

Qui diciamo chiaramente a PostgreSQL che il separatore è la barra verticale.

Configurazione dei formati dati durante il caricamento

I separatori sono solo una parte del lavoro. Anche il formato dei dati nel file è importante. Vediamo i modi principali per configurare i formati dei dati.

Ignorare le righe vuote e impostare NULL

Spesso nei grandi dataset ci sono righe o colonne vuote, senza dati. PostgreSQL le vede come stringhe vuote, a meno che tu non dica diversamente. Per interpretare questi valori come NULL puoi usare il parametro NULL AS:

Esempio. Supponiamo che nel tuo file ci siano record con valori vuoti:

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

Caricamento dei dati interpretando i valori vuoti nella colonna email come NULL:

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

Così i valori vuoti nel file saranno salvati come NULL nella tabella.

Ignorare le righe vuote

A volte il file può avere righe vuote che non vuoi caricare. PostgreSQL ti permette di ignorare queste righe in automatico.

Esempio. File con una riga vuota:

id,nome,cognome,email
1,John,Doe,john.doe@example.com

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

Usiamo il parametro IGNORE_BLANK_LINES:

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

Ora le righe vuote verranno ignorate durante il caricamento.

Lavorare con formati dati non standard

A volte potresti dover caricare dati in formato testo invece che CSV. Ad esempio, le righe nel file sono separate da barre verticali | e i dati non hanno la riga di intestazione.

Esempio di file:

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

In questo caso puoi usare questa query:

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

Se il file non ha intestazioni, basta togliere il parametro HEADER.

Esempio pratico di configurazione dei formati dati

Scenario: hai un file grades.tsv che contiene i voti degli studenti. I dati sono così:

id_studente  id_corso   voto
1   101     85
1   102     90
2   101     78
2   102     88
3   101     95
3   102 

Devi:

1. Caricare il file interpretando correttamente i valori vuoti come NULL.
2. Assicurarti che i dati siano caricati correttamente.

Soluzione:

1. Crea la tabella grades:

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

2. Carica i dati dal file:

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

3. Controlla i dati caricati:

SELECT * FROM grades;

Risultato atteso:

Consigli e errori tipici

Errore: separatore sbagliato. Se non specifichi il separatore giusto, PostgreSQL darà errore o caricherà i dati in modo sbagliato. Ad esempio, se nel file c'è il punto e virgola ma ti dimentichi di mettere DELIMITER ';', il comando COPY prenderà tutta la riga come una sola colonna.

Errore: interpretazione sbagliata di NULL. Se non metti il parametro NULL AS '', i valori vuoti nel file possono essere interpretati come stringhe vuote, e questo spesso causa errori nei calcoli o nei filtri.

Errore: formato dati sbagliato. Una configurazione errata del separatore o errori nel file (tipo usare la tabulazione invece degli spazi) può portare a un errore tipo: ERROR: null value in column violates not-null constraint.