Upsert patterns: INSERT ... ON CONFLICT em ação

1. O que é Upsert e por que ele existe?

Upsert é uma operação que combina INSERT e UPDATE em uma única instrução atômica. O nome vem da junção de "update" + "insert". Quando você tenta inserir um registro que já existe (conflito de chave única ou primária), o banco automaticamente executa um UPDATE no registro existente.

O problema clássico resolvido pelo upsert é a race condition. Considere o cenário tradicional:

-- Abordagem problemática (não atômica)
SELECT * FROM usuarios WHERE email = 'joao@email.com';
-- Se não existir:
INSERT INTO usuarios (email, nome) VALUES ('joao@email.com', 'João');
-- Se existir:
UPDATE usuarios SET nome = 'João' WHERE email = 'joao@email.com';

Entre o SELECT e o INSERT/UPDATE, outra transação pode inserir o mesmo registro, causando erro de duplicidade. O upsert resolve isso com atomicidade.

Comparado a alternativas:
- SELECT + INSERT/UPDATE: vulnerável a race conditions
- MERGE (SQL:2003): mais verboso, propenso a erros de sintaxe, menos performático em alguns bancos
- INSERT ... ON CONFLICT: atômico, legível, otimizado pelo planner

2. Sintaxe fundamental do INSERT ... ON CONFLICT

A estrutura básica no PostgreSQL é:

INSERT INTO tabela (coluna1, coluna2, coluna3)
VALUES (valor1, valor2, valor3)
ON CONFLICT (coluna_conflito) DO UPDATE SET
    coluna2 = EXCLUDED.coluna2,
    coluna3 = EXCLUDED.coluna3;

Você pode especificar a constraint alvo de três formas:
- Coluna única: ON CONFLICT (id)
- Colunas compostas: ON CONFLICT (email, tipo)
- Constraint nomeada: ON CONFLICT ON CONSTRAINT usuarios_pkey

A cláusula DO NOTHING é útil quando você quer ignorar silenciosamente duplicatas:

INSERT INTO logs (evento, timestamp)
VALUES ('login', NOW())
ON CONFLICT (evento, timestamp) DO NOTHING;

3. Comportamento do ON CONFLICT DO UPDATE SET

A pseudo-tabela EXCLUDED é o coração do upsert. Ela contém os valores que você tentou inserir originalmente. Os valores existentes na tabela são referenciados diretamente pelo nome da coluna.

INSERT INTO contadores (pagina, visualizacoes)
VALUES ('/home', 1)
ON CONFLICT (pagina) DO UPDATE SET
    visualizacoes = contadores.visualizacoes + EXCLUDED.visualizacoes,
    ultima_atualizacao = NOW();

Aqui:
- contadores.visualizacoes → valor existente no banco
- EXCLUDED.visualizacoes → valor que tentamos inserir (1)

Cuidado com atualizações parciais: colunas não mencionadas no SET mantêm seus valores originais. Se você espera que todas as colunas sejam atualizadas, precisa listá-las explicitamente.

INSERT INTO usuarios (id, nome, email, ativo)
VALUES (1, 'Maria', 'maria@email.com', true)
ON CONFLICT (id) DO UPDATE SET
    nome = EXCLUDED.nome,
    email = EXCLUDED.email;
-- 'ativo' NÃO será alterado se o registro já existir

4. Upsert com conflito em múltiplas colunas (chave composta)

Para chaves compostas, especifique todas as colunas da constraint:

CREATE TABLE inscricoes (
    usuario_id INT,
    curso_id INT,
    data_inscricao TIMESTAMP,
    progresso DECIMAL(5,2),
    PRIMARY KEY (usuario_id, curso_id)
);

INSERT INTO inscricoes (usuario_id, curso_id, data_inscricao, progresso)
VALUES (101, 5, NOW(), 0.0)
ON CONFLICT (usuario_id, curso_id) DO UPDATE SET
    progresso = EXCLUDED.progresso,
    data_inscricao = CASE
        WHEN inscricoes.progresso < EXCLUDED.progresso
        THEN EXCLUDED.data_inscricao
        ELSE inscricoes.data_inscricao
    END;

Isso é particularmente útil em tabelas many-to-many que armazenam metadados adicionais sobre o relacionamento.

5. Tratamento de conflitos parciais com WHERE no UPDATE

Você pode adicionar uma condição ao DO UPDATE para atualizar apenas em cenários específicos:

INSERT INTO precos (produto_id, valor, atualizado_em)
VALUES (42, 150.00, NOW())
ON CONFLICT (produto_id) DO UPDATE SET
    valor = EXCLUDED.valor,
    atualizado_em = EXCLUDED.atualizado_em
WHERE EXCLUDED.valor > precos.valor;

Neste exemplo, o preço só é atualizado se o novo valor for maior que o existente. Combinando WHERE com EXCLUDED, você cria lógicas condicionais complexas:

INSERT INTO estoque (produto_id, quantidade, versao)
VALUES (10, 50, 1)
ON CONFLICT (produto_id) DO UPDATE SET
    quantidade = EXCLUDED.quantidade,
    versao = estoque.versao + 1
WHERE estoque.versao = EXCLUDED.versao - 1; -- Controle de concorrência otimista

6. Performance e considerações de implementação

O PostgreSQL otimiza o upsert de forma inteligente. O planner primeiro tenta o INSERT. Se detecta um conflito potencial (via índice único), ele prepara o UPDATE. Isso é mais eficiente que:

  • INSERT + tratamento de erro + UPDATE: gera logs de erro desnecessários
  • SELECT + INSERT/UPDATE em transação: duas operações de I/O e locking adicional

Limitações conhecidas:
- Triggers: triggers BEFORE INSERT e BEFORE UPDATE disparam separadamente. O trigger BEFORE INSERT sempre executa, mesmo quando o conflito resulta em UPDATE.
- Foreign keys: a verificação de chave estrangeira ocorre normalmente, mas pode haver deadlocks em alta concorrência.
- Locking: o upsert adquire um lock na linha conflitante, similar a um UPDATE normal.

7. Padrões avançados e casos de borda

Capturando o resultado com RETURNING:

WITH upsert AS (
    INSERT INTO usuarios (email, nome)
    VALUES ('ana@email.com', 'Ana')
    ON CONFLICT (email) DO UPDATE SET
        nome = EXCLUDED.nome,
        atualizado_em = NOW()
    RETURNING *, (xmax = 0) AS foi_inserido
)
SELECT * FROM upsert;

xmax = 0 indica que o registro foi inserido (não existia antes). Isso permite distinguir entre INSERT e UPDATE no resultado.

Upsert em tabelas sem chave primária: use um unique index:

CREATE UNIQUE INDEX idx_sessao_unica ON sessoes (usuario_id, dispositivo) WHERE ativa = true;

INSERT INTO sessoes (usuario_id, dispositivo, token, ativa)
VALUES (1, 'mobile', 'abc123', true)
ON CONFLICT (usuario_id, dispositivo) WHERE ativa = true
DO UPDATE SET token = EXCLUDED.token, criada_em = NOW();

Estratégias de retry para deadlocks: em sistemas concorrentes, envolva o upsert em um loop com retry:

DO $$
DECLARE
    tentativas INT := 0;
BEGIN
    LOOP
        BEGIN
            INSERT INTO contadores (id, valor) VALUES (1, 1)
            ON CONFLICT (id) DO UPDATE SET valor = contadores.valor + 1;
            EXIT;
        EXCEPTION WHEN deadlock_detected THEN
            tentativas := tentativas + 1;
            IF tentativas > 3 THEN RAISE; END IF;
            PERFORM pg_sleep(0.1 * tentativas);
        END;
    END LOOP;
END $$;

8. Migrando de MERGE para INSERT ... ON CONFLICT

O MERGE (SQL:2003) é suportado no PostgreSQL 15+, mas o INSERT ... ON CONFLICT oferece vantagens:

MERGE tradicional:

MERGE INTO estoque AS e
USING (VALUES (1, 100)) AS v(produto_id, quantidade)
ON e.produto_id = v.produto_id
WHEN MATCHED THEN UPDATE SET quantidade = e.quantidade + v.quantidade
WHEN NOT MATCHED THEN INSERT (produto_id, quantidade) VALUES (v.produto_id, v.quantidade);

Equivalente com INSERT ... ON CONFLICT:

INSERT INTO estoque (produto_id, quantidade)
VALUES (1, 100)
ON CONFLICT (produto_id) DO UPDATE SET
    quantidade = estoque.quantidade + EXCLUDED.quantidade;

Vantagens do INSERT ... ON CONFLICT:
- Atomicidade: uma única instrução, sem transação explícita
- Legibilidade: menos cláusulas, sintaxe mais direta
- Performance: melhor otimizado pelo planner do PostgreSQL
- Menos propenso a erros: o MERGE tem casos de borda complexos com triggers e locking

Para migrar, identifique seus MERGE statements e substitua pela estrutura equivalente. A maioria dos casos se traduz diretamente, exceto WHEN MATCHED com condições complexas que podem exigir o WHERE no DO UPDATE.

Referências