Como escrever um upsert com INSERT ... ON CONFLICT

Objetivo

Inserir uma linha quando a chave é nova e, caso contrário, atualizar a linha existente, sem condição de corrida entre SELECT e INSERT e sem laços de nova tentativa no código da aplicação.

Pré-requisitos

Um índice único ou uma constraint nas colunas da chave (a documentação do INSERT chama de índice árbitro o índice escolhido para detectar o conflito e descreve como ele é inferido: a partir das colunas listadas, de um predicado de índice ou de ON CONSTRAINT name) e uma decisão sobre quais colunas uma atualização pode sobrescrever.

Passos

1. Escreva o INSERT simples com todas as colunas.
2. Acrescente ON CONFLICT (key_columns) com as colunas do índice único. Se o índice único for parcial, repita o predicado WHERE dele depois da lista de colunas, para que a inferência o encontre.
3. Escolha a ação: DO NOTHING quando a linha existente deve permanecer intacta; DO UPDATE SET col = EXCLUDED.col, updated_at = now() para sobrescrever colunas selecionadas. EXCLUDED é a linha que foi proposta para inserção.
4. Proteja contra atualizações sem efeito com WHERE t.col IS DISTINCT FROM EXCLUDED.col depois da lista do SET, para que linhas inalteradas não sejam reescritas; cada atualização cria uma versão de linha morta e dispara triggers.
5. Adicione RETURNING id quando quem chama precisar da chave da linha inserida ou atualizada.
6. Para lotes, use um único INSERT com várias linhas em VALUES ou INSERT ... SELECT; não percorra upserts de uma linha em um laço.
7. Quando a lógica tiver vários ramos (atualizar se houver correspondência e uma condição, excluir se houver correspondência em caso contrário, inserir se não houver correspondência), escreva MERGE INTO target USING source ON ... WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT ...; a documentação do MERGE lista as ações WHEN MATCHED, WHEN NOT MATCHED e WHEN NOT MATCHED BY SOURCE.

Resultado esperado

Uma instrução por upsert, sem linhas duplicadas sob escritores concorrentes, porque o conflito é detectado no índice único dentro da própria instrução, e com o acúmulo de linhas mortas limitado às linhas que de fato mudaram.

Limites e base de verificação

ON CONFLICT DO UPDATE exige um alvo de conflito; só são tratados os conflitos nos índices árbitros que ele seleciona (a violação de outro índice único ainda gera erro), e apenas constraints NOT DEFERRABLE e índices únicos podem servir como árbitros. DO NOTHING sem alvo de conflito trata conflitos com todas as constraints utilizáveis. A documentação de sequências afirma que um INSERT com cláusula ON CONFLICT calcula a linha, inclusive as chamadas a nextval, antes de detectar o conflito; por isso valores de identidade são consumidos por linhas que acabam não sendo inseridas. A documentação do MERGE diz que valem as regras usuais de isolamento sob concorrência e aponta INSERT ... ON CONFLICT como a instrução capaz de executar um UPDATE quando ocorre um INSERT concorrente; as duas não são intercambiáveis. Nenhum tempo de execução é afirmado.

Quando MERGE é a ferramenta certa

MERGE e INSERT ... ON CONFLICT não são intercambiáveis sob concorrência. O MERGE avalia a correspondência sobre um snapshot e não faz a inserção especulativa em que o ON CONFLICT se baseia; assim, duas sessões que façam merge da mesma chave nova podem ambas seguir o ramo WHEN NOT MATCHED, e a segunda falha com uma violação de unicidade. Use MERGE quando apenas um escritor por vez for dono da faixa de chaves: cargas em lote, migrações, importadores de thread única ou linhas bloqueadas antecipadamente. Use ON CONFLICT sempre que houver possibilidade de escritores concorrentes; vários ramos de atualização cabem em expressões CASE na lista do SET, e um ramo de exclusão vira uma instrução separada. WHEN NOT MATCHED BY SOURCE e RETURNING no MERGE exigem PostgreSQL 17 ou superior.