UPSERT significa «inserir uma linha, ou atualizá-la se ela já existir» em um único comando atômico. No PostgreSQL essa tarefa cabe ao INSERT ... ON CONFLICT. Ele elimina a clássica condição de corrida em que você primeiro faz um SELECT, não encontra nada, faz o INSERT — e então esbarra em uma violação de unicidade porque uma transação concorrente inseriu a mesma linha no meio do caminho. O ON CONFLICT resolve isso dentro do motor, sem bloqueio explícito e sem o laço de «tenta inserir, captura o erro, atualiza».
Aqui está um esquema funcional e exemplos prontos para copiar e colar no psql.
Sintaxe básica: DO NOTHING e DO UPDATE
Vamos começar com uma tabela de usuários que tem um e-mail único:
CREATE TABLE users (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
email text NOT NULL UNIQUE,
name text NOT NULL,
visits int NOT NULL DEFAULT 0,
updated_at timestamptz NOT NULL DEFAULT now()
);
O DO NOTHING ignora silenciosamente um conflito em vez de gerar um erro:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann')
ON CONFLICT (email) DO NOTHING;
O DO UPDATE, em vez disso, atualiza a linha existente. Um detalhe importante: depois de ON CONFLICT você especifica o alvo do conflito (conflict target) — a coluna (ou colunas) sustentada por um índice ou restrição únicos que o PostgreSQL usa para detectar a colisão:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann Smith')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name,
updated_at = now();
- O alvo do conflito precisa corresponder a um índice único ou PK reais. Sem um índice correspondente, o PostgreSQL rejeita o comando.
- Você pode mirar pelo nome da restrição:
ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....
EXCLUDED: os valores da inserção rejeitada
O EXCLUDED é uma pseudotabela que guarda a linha que você tentou inserir mas não conseguiu por causa do conflito. Dentro do DO UPDATE você puxa de lá os valores «novos» em vez de redigitá-los. Isso é especialmente útil em inserções em lote:
INSERT INTO users (email, name)
VALUES ('ann@example.com', 'Ann'),
('bob@example.com', 'Bob')
ON CONFLICT (email)
DO UPDATE SET name = EXCLUDED.name;
Você pode mesclar o antigo com o novo — por exemplo, só sobrescrever quando chega um nome não vazio:
INSERT INTO users (email, name)
VALUES ('ann@example.com', NULL)
ON CONFLICT (email)
DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);
Aqui users.name é o valor atual na tabela e EXCLUDED.name é o que você tentou gravar. Isso dá um controle granular sobre quais colunas sobrescrever e quais manter.
Inserções idempotentes e WHERE no DO UPDATE
Idempotência significa que executar o mesmo comando de novo não altera o resultado. Isso importa para entrega de mensagens «pelo menos uma vez», retentativas e importações que podem ser reexecutadas. A forma mais simples é o DO NOTHING sobre uma chave natural:
INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'paid')
ON CONFLICT (order_id) DO NOTHING;
Execute dez vezes e a linha aparece exatamente uma vez. Se você quiser de fato atualizar, mas só quando os dados realmente mudaram, adicione um WHERE ao DO UPDATE — isso evita gravações inúteis e impede que os triggers disparem à toa:
INSERT INTO orders (order_id, user_id, amount, status)
VALUES ('ORD-1001', 42, 199.00, 'shipped')
ON CONFLICT (order_id)
DO UPDATE SET status = EXCLUDED.status,
updated_at = now()
WHERE orders.status IS DISTINCT FROM EXCLUDED.status;
O IS DISTINCT FROM compara valores de forma segura mesmo quando há NULL envolvido. Se o status não mudou, nenhuma atualização acontece.
Contadores atômicos
Uma tarefa clássica: contar visitas ou visualizações de página sem condições de corrida. Em vez de SELECT ... + UPDATE, faça o incremento direto dentro do DO UPDATE, referenciando o valor atual da linha:
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON CONFLICT (email)
DO UPDATE SET visits = users.visits + 1,
updated_at = now();
A primeira chamada cria a linha com visits = 1; cada chamada subsequente incrementa o contador em um de forma atômica. As transações concorrentes ficam enfileiradas no nível da linha, então nenhuma atualização é perdida. Adicione RETURNING para ler o novo valor de imediato:
... DO UPDATE SET visits = users.visits + 1
RETURNING visits;
Armadilhas e a diferença para o MySQL
- Múltiplos conflitos em um único comando. Se uma linha viola dois índices únicos diferentes ao mesmo tempo, o
ON CONFLICT só trata o alvo que você nomeou — o outro índice ainda lança um erro.
- Duplicatas dentro de uma mesma lista VALUES. Você não pode atualizar a mesma linha-alvo duas vezes em um único comando:
ON CONFLICT DO UPDATE command cannot affect row a second time. Remova as duplicatas da sua entrada antes de inserir.
- Tabelas particionadas. O alvo do conflito precisa incluir a chave de particionamento.
O equivalente no MySQL é o INSERT ... ON DUPLICATE KEY UPDATE. Ele dispara em qualquer chave única (nenhum alvo é nomeado) e, em vez de EXCLUDED, você usa a função VALUES() (um alias de linha nas versões mais recentes):
INSERT INTO users (email, name, visits)
VALUES ('ann@example.com', 'Ann', 1)
ON DUPLICATE KEY UPDATE
name = VALUES(name),
visits = visits + 1;
O ClickHouse não tem um UPSERT de verdade: o motor não impõe unicidade em tempo de inserção. Os padrões usuais são o ReplacingMergeTree (a deduplicação acontece em segundo plano durante as merges) ou um INSERT simples seguido de filtragem no momento da leitura. Se você precisa de inserções idempotentes estritas e contadores atômicos agora mesmo, esse é o território do PostgreSQL e do MySQL — não o dos bancos colunares analíticos.
UPSERT significa «inserir uma linha, ou atualizá-la se ela já existir» em um único comando atômico. No PostgreSQL essa tarefa cabe ao
INSERT ... ON CONFLICT. Ele elimina a clássica condição de corrida em que você primeiro faz umSELECT, não encontra nada, faz oINSERT— e então esbarra em uma violação de unicidade porque uma transação concorrente inseriu a mesma linha no meio do caminho. OON CONFLICTresolve isso dentro do motor, sem bloqueio explícito e sem o laço de «tenta inserir, captura o erro, atualiza».Aqui está um esquema funcional e exemplos prontos para copiar e colar no
psql.Sintaxe básica: DO NOTHING e DO UPDATE
Vamos começar com uma tabela de usuários que tem um e-mail único:
CREATE TABLE users ( id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL UNIQUE, name text NOT NULL, visits int NOT NULL DEFAULT 0, updated_at timestamptz NOT NULL DEFAULT now() );O
DO NOTHINGignora silenciosamente um conflito em vez de gerar um erro:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann') ON CONFLICT (email) DO NOTHING;O
DO UPDATE, em vez disso, atualiza a linha existente. Um detalhe importante: depois deON CONFLICTvocê especifica o alvo do conflito (conflict target) — a coluna (ou colunas) sustentada por um índice ou restrição únicos que o PostgreSQL usa para detectar a colisão:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann Smith') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name, updated_at = now();ON CONFLICT ON CONSTRAINT users_email_key DO UPDATE ....EXCLUDED: os valores da inserção rejeitada
O
EXCLUDEDé uma pseudotabela que guarda a linha que você tentou inserir mas não conseguiu por causa do conflito. Dentro doDO UPDATEvocê puxa de lá os valores «novos» em vez de redigitá-los. Isso é especialmente útil em inserções em lote:INSERT INTO users (email, name) VALUES ('ann@example.com', 'Ann'), ('bob@example.com', 'Bob') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;Você pode mesclar o antigo com o novo — por exemplo, só sobrescrever quando chega um nome não vazio:
INSERT INTO users (email, name) VALUES ('ann@example.com', NULL) ON CONFLICT (email) DO UPDATE SET name = COALESCE(EXCLUDED.name, users.name);Aqui
users.nameé o valor atual na tabela eEXCLUDED.nameé o que você tentou gravar. Isso dá um controle granular sobre quais colunas sobrescrever e quais manter.Inserções idempotentes e WHERE no DO UPDATE
Idempotência significa que executar o mesmo comando de novo não altera o resultado. Isso importa para entrega de mensagens «pelo menos uma vez», retentativas e importações que podem ser reexecutadas. A forma mais simples é o
DO NOTHINGsobre uma chave natural:INSERT INTO orders (order_id, user_id, amount, status) VALUES ('ORD-1001', 42, 199.00, 'paid') ON CONFLICT (order_id) DO NOTHING;Execute dez vezes e a linha aparece exatamente uma vez. Se você quiser de fato atualizar, mas só quando os dados realmente mudaram, adicione um
WHEREaoDO UPDATE— isso evita gravações inúteis e impede que os triggers disparem à toa:INSERT INTO orders (order_id, user_id, amount, status) VALUES ('ORD-1001', 42, 199.00, 'shipped') ON CONFLICT (order_id) DO UPDATE SET status = EXCLUDED.status, updated_at = now() WHERE orders.status IS DISTINCT FROM EXCLUDED.status;O
IS DISTINCT FROMcompara valores de forma segura mesmo quando háNULLenvolvido. Se o status não mudou, nenhuma atualização acontece.Contadores atômicos
Uma tarefa clássica: contar visitas ou visualizações de página sem condições de corrida. Em vez de
SELECT ... + UPDATE, faça o incremento direto dentro doDO UPDATE, referenciando o valor atual da linha:INSERT INTO users (email, name, visits) VALUES ('ann@example.com', 'Ann', 1) ON CONFLICT (email) DO UPDATE SET visits = users.visits + 1, updated_at = now();A primeira chamada cria a linha com
visits = 1; cada chamada subsequente incrementa o contador em um de forma atômica. As transações concorrentes ficam enfileiradas no nível da linha, então nenhuma atualização é perdida. AdicioneRETURNINGpara ler o novo valor de imediato:... DO UPDATE SET visits = users.visits + 1 RETURNING visits;Armadilhas e a diferença para o MySQL
ON CONFLICTsó trata o alvo que você nomeou — o outro índice ainda lança um erro.ON CONFLICT DO UPDATE command cannot affect row a second time. Remova as duplicatas da sua entrada antes de inserir.O equivalente no MySQL é o
INSERT ... ON DUPLICATE KEY UPDATE. Ele dispara em qualquer chave única (nenhum alvo é nomeado) e, em vez deEXCLUDED, você usa a funçãoVALUES()(um alias de linha nas versões mais recentes):-- MySQL INSERT INTO users (email, name, visits) VALUES ('ann@example.com', 'Ann', 1) ON DUPLICATE KEY UPDATE name = VALUES(name), visits = visits + 1;O ClickHouse não tem um UPSERT de verdade: o motor não impõe unicidade em tempo de inserção. Os padrões usuais são o
ReplacingMergeTree(a deduplicação acontece em segundo plano durante as merges) ou umINSERTsimples seguido de filtragem no momento da leitura. Se você precisa de inserções idempotentes estritas e contadores atômicos agora mesmo, esse é o território do PostgreSQL e do MySQL — não o dos bancos colunares analíticos.