Configurando delimitadores e formatos de dados na importação

Quando os dados vêm de vários sistemas, o formato deles pode ser bem diferente. Um arquivo pode usar vírgulas como delimitador, outro pode usar ponto e vírgula, e em outro a tabulação pode ser o principal jeito de separar as colunas. Se tu não configurar direito o delimitador na hora de importar, pode dar erro ou os dados podem ser interpretados errado.

Além disso, na vida real tu pode topar com situações em que precisa importar dados em formato texto sem os headers padrão do CSV, além de lidar com valores vazios e prever casos em que linhas em branco devem ser interpretadas como NULL. Por isso, configurar delimitadores e formatos é parte essencial do trampo de importar dados em massa.

Delimitadores principais: como lidar com eles?

No PostgreSQL, o comando COPY é bem flexível pra configurar delimitadores. Bora ver como funciona.

Configurando o delimitador

Por padrão, o comando COPY espera que as colunas no arquivo CSV estejam separadas por vírgula. Mas nem sempre isso é prático ou possível: alguém pode usar ponto e vírgula (;), barra vertical (|) ou até tabulação (\t).

Olha como tu pode definir o delimitador usando o parâmetro DELIMITER:

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

Se em vez de vírgula for ponto e vírgula:

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

Dá até pra importar arquivo separado por tabulação:

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

Aqui o E'\t' indica que o delimitador é o caractere de tabulação.

Importando arquivos com delimitador diferente

Exemplo prático: tu tem um arquivo com dados dos cursos, usando barra vertical (|) como delimitador. Os dados no arquivo tão assim:

course_id|course_name|credits
1|SQL Básico|3
2|SQL Avançado|4
3|PostgreSQL Masterclass|5

Olha como importar esses dados pra tabela courses:

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

Aqui a gente fala pro PostgreSQL que o delimitador é a barra vertical.

Configurando formatos de dados na importação

Delimitador é só parte do rolê. O formato dos dados no arquivo também é importante. Bora ver os jeitos principais de configurar os formatos.

Ignorando linhas vazias e definindo NULL

Em arquivos grandes, é comum ter linhas ou colunas vazias, sem dado nenhum. O PostgreSQL entende isso como string vazia, a não ser que tu diga o contrário. Pra interpretar esses valores como NULL, usa o parâmetro NULL AS:

Exemplo. Imagina que teu arquivo tem registros com valores vazios:

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

Importando os dados e interpretando valores vazios na coluna email como NULL:

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

No fim, os valores vazios do arquivo vão virar NULL na tabela.

Ignorando linhas em branco

Às vezes o arquivo tem linhas em branco que tu não quer importar. O PostgreSQL pode ignorar essas linhas automaticamente.

Exemplo. Arquivo com linha em branco:

id,primeiro_nome,sobrenome,email
1,John,Doe,john.doe@example.com

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

Usa o parâmetro IGNORE_BLANK_LINES:

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

Agora as linhas em branco vão ser ignoradas na importação.

Lidando com formato de dados diferente

Às vezes tu precisa importar dados em formato texto, não CSV. Por exemplo, as linhas do arquivo são separadas por barra vertical | e não tem linha de header.

Exemplo de arquivo:

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

Nesse caso, tu pode usar essa query:

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

Se o arquivo não tem header, só tira o parâmetro HEADER.

Exemplo prático de configuração de formatos de dados

Cenário: tu tem um arquivo grades.tsv com as notas dos estudantes. Os dados tão assim:

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

O que precisa fazer:

1. Importar o arquivo interpretando valores vazios como NULL.
2. Garantir que os dados foram importados certinho.

Solução:

1. Cria a tabela grades:

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

2. Importa os dados do arquivo:

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

3. Confere os dados importados:

SELECT * FROM grades;

Resultado esperado:

Dicas e erros comuns

Erro: delimitador errado. Se tu não colocar o delimitador certo, o PostgreSQL vai dar erro ou importar os dados tudo errado. Por exemplo, se o arquivo usa ponto e vírgula mas tu esquece de botar DELIMITER ';', o comando COPY vai achar que a linha toda é uma coluna só.

Erro: interpretação errada do NULL. Se tu não usar o parâmetro NULL AS '', os valores vazios do arquivo podem virar string vazia, o que costuma dar erro em cálculo ou filtro depois.

Erro: formato de dado errado. Se tu configurar o delimitador errado ou tiver erro no arquivo (tipo usar tabulação em vez de espaço), pode dar erro tipo: ERROR: null value in column violates not-null constraint.