Tratamento de erros ao carregar dados ( ON CONFLICT)

Seja bem-vindo ao centro dos cenários dramáticos de carga massiva de dados! Hoje a gente vai aprender a lidar de forma eficiente com os erros que aparecem quando você tá carregando dados, usando o ON CONFLICT. É tipo ligar o piloto automático no avião: mesmo se der ruim, você vai saber o que fazer pra não cair tudo. Bora desvendar os truques do PostgreSQL!

Ninguém curte surpresa, ainda mais quando os dados se recusam a entrar! Em cargas massivas, você pode trombar com alguns problemas clássicos:

• Duplicidade de dados. Por exemplo, se a tabela tem uma restrição UNIQUE e seu arquivo de dados tá cheio de repetidos.
• Conflitos com restrições. Tipo tentar inserir um valor vazio numa coluna que tem NOT NULL. Resultado? Erro. O PostgreSQL é todo certinho nessas horas.
• Informação primária duplicada. A tabela pode já ter dados com os mesmos IDs que estão no seu arquivo CSV.

Bora ver como evitar essas "armadilhas" com o ON CONFLICT.

Usando ON CONFLICT pra tratar erros

A sintaxe do ON CONFLICT deixa você dizer o que fazer quando rola conflito com restrições (tipo UNIQUE ou PRIMARY KEY). O PostgreSQL te dá a opção de atualizar os dados existentes ou só ignorar a linha que deu ruim.

Olha como é a sintaxe básica do ON CONFLICT:

INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...)
ON CONFLICT (conflict_target)
DO UPDATE SET column1 = new_value1, column2 = new_value2;

Você pode trocar o DO UPDATE por DO NOTHING se quiser só ignorar o conflito.

Exemplo: atualizando dados quando rola conflito

Imagina que temos uma tabela students:

CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT
);

Agora queremos carregar novos dados, mas alguns já existem no banco:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Esse estudante já existe
    (2, 'Anna', 20),  -- Estudante novo
    (3, 'Mal', 25) -- Estudante novo
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

Nesse exemplo, se já tiver um estudante com o ID que você quer inserir, os dados dele vão ser atualizados:

ON CONFLICT (id) DO UPDATE SET
    name = EXCLUDED.name, 
    age = EXCLUDED.age;

Repara na palavra mágica EXCLUDED. Ela significa "os valores que você tentou inserir, mas foram barrados por causa do conflito".

Resultado:

• O estudante com id = 1 vai ter os dados (nome e idade) atualizados.
• Os estudantes com id = 2 e id = 3 vão ser adicionados na tabela.

Exemplo: ignorando conflitos

Se você não quiser atualizar nada, só ignorar as linhas que dão conflito, usa o DO NOTHING:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22),  -- Esse estudante já existe
    (2, 'Anna', 20),  -- Estudante novo
    (3, 'Mal', 25) -- Estudante novo
ON CONFLICT (id) DO NOTHING;

Agora as linhas que deram conflito não vão ser inseridas, e o resto entra de boa no seu banco.

Logando erros

Às vezes só ignorar ou atualizar não resolve. Tipo, você precisa registrar os conflitos pra analisar depois. Dá pra criar uma tabela especial pra logar os erros:

CREATE TABLE conflict_log (
    conflict_time TIMESTAMP DEFAULT NOW(),
    id INT,
    name TEXT,
    age INT,
    conflict_reason TEXT
);

Depois, bora adicionar o tratamento de erro com log:

INSERT INTO students (id, name, age)
VALUES 
    (1, 'Peter', 22), 
    (2, 'Anna', 20), 
    (3, 'Mal', 25)
ON CONFLICT (id) DO UPDATE SET 
    name = EXCLUDED.name, 
    age = EXCLUDED.age
RETURNING EXCLUDED.id, EXCLUDED.name, EXCLUDED.age
INTO conflict_log;

Esse último exemplo só vai funcionar dentro de stored procedures. Como isso funciona você vai sacar quando a gente estudar PL-SQL. Tô adiantando um pouco só pra mostrar mais um jeito de resolver conflito de carga de dados: logar todas as linhas problemáticas.

Agora você pode analisar os motivos dos conflitos. Essa técnica é muito útil em sistemas grandes, onde é importante guardar o "rastro" das cargas massivas de dados.

Exemplo prático

Bora juntar tudo que aprendemos num exercício simples. Imagina que você tem um arquivo CSV com atualizações de estudantes que quer carregar na tabela:

Arquivo students_update.csv

Carregando dados e tratando conflitos

1. Primeiro, cria uma tabela temporária tmp_students:

CREATE TEMP TABLE tmp_students (
  id   INTEGER,
  name TEXT,
  age  INTEGER
);

2. Carrega os dados do arquivo usando \COPY:

\COPY tmp_students FROM 'students_update.csv' DELIMITER ',' CSV HEADER

3. Insere os dados da tabela temporária na definitiva usando INSERT ON CONFLICT:

INSERT INTO students (id, name, age)
SELECT id, name, age FROM tmp_students
ON CONFLICT (id) DO UPDATE
  SET name = EXCLUDED.name,
      age = EXCLUDED.age;

Agora todos os dados, incluindo as atualizações (linha com id = 1), foram carregados com sucesso.

Erros comuns e como evitar

Erros acontecem até com os devs mais experientes, mas sabendo como evitar, você economiza horas (ou até dias) de dor de cabeça.

• Conflito com restrição UNIQUE. Confere se você colocou o campo certo no ON CONFLICT. Por exemplo, se você botar a chave errada (id em vez de email), o PostgreSQL vai só dar "tchau" pra sua query.
• Uso errado do EXCLUDED. Esse apelido só serve pros valores passados na query atual. Não tenta usar ele em outros contextos.
• Colunas faltando. Confere se todas as colunas que você botou no SET existem na tabela. Tipo, se você colocar SET non_existing_column = 'value', vai dar erro.

Usar ON CONFLICT deixa a carga massiva de dados no PostgreSQL flexível e segura. Você não só evita que as queries falhem por causa de conflitos, mas também controla como seus dados vão ser tratados. Seus usuários (e seus servidores!) vão te agradecer.