O PostgreSQL não tem a palavra-chave UPSERT. Você escreve INSERT ... ON CONFLICT (column) DO UPDATE SET column = EXCLUDED.column. Quando a nova linha violaria uma restrição única nessa coluna, o PostgreSQL atualiza a linha existente no lugar. DO NOTHING a ignora.
A-100 já existia, então o preço mudou para 12.5. B-200 era novo e foi inserido. Um único comando resolve os dois casos, e continua correto quando outras sessões inserem as mesmas chaves ao mesmo tempo.
Inserir ou atualizar uma linha
O comando tem três partes:
INSERT INTO table_name (columns) VALUES (...)
ON CONFLICT (conflict_target)
DO UPDATE SET column = value [, ...]
[WHERE condition];
- O alvo do conflito nomeia as colunas de um índice ou restrição única, como a chave primária. Ele precisa bater exatamente com um, senão o PostgreSQL lança "there is no unique or exclusion constraint matching the ON CONFLICT specification". Você também pode nomear a restrição:
ON CONFLICT ON CONSTRAINT products_pkey. EXCLUDEDé a linha que você tentou inserir.EXCLUDED.priceé o preço novo, eproducts.priceé o preço já gravado.DO UPDATE SETaltera só as colunas que você listar. As outras colunas da linha existente mantêm os valores.
Um índice único com cláusula WHERE (um índice parcial) também serve de alvo, se você repetir a condição dele: ON CONFLICT (email) WHERE deleted_at IS NULL.
Ignorar duplicados com DO NOTHING
DO NOTHING insere as linhas novas e descarta as que colidem, sem erro. Sem alvo de conflito, ignora uma linha que viole qualquer restrição única.
O RETURNING lista só lin@example.com. Uma linha ignorada não é devolvida, então DO NOTHING ... RETURNING mostra quais linhas eram novas.
Somar num contador
Para aumentar um valor gravado em vez de substituí-lo, some EXCLUDED ao valor atual. Qualifique o valor atual com o nome da tabela. Dentro do DO UPDATE, um views sozinho corresponde tanto à tabela quanto ao EXCLUDED, e o PostgreSQL para com "column reference "views" is ambiguous".
O primeiro comando insere a linha com 1 e os dois seguintes somam a ela, então /home termina em 3.
Atualizar só quando o valor mudou
Um WHERE depois de DO UPDATE SET decide, linha a linha, se o update roda. Quando é falso, a linha fica como está e não conta nem como inserida nem como atualizada. Use para manter só os dados mais recentes, ou para não gravar uma nova versão da linha quando nada mudou.
A-100 mantém 10 porque a linha que chegou é mais antiga. B-200 passa para 25. Para pular updates que gravariam os mesmos valores, use WHERE prices.price IS DISTINCT FROM EXCLUDED.price, que também trata NULL.
Diferenciar linhas inseridas de atualizadas
RETURNING funciona num upsert como em qualquer insert. Para diferenciar os dois casos, leia a coluna de sistema xmax, que é 0 numa linha que este comando inseriu:
A-100 volta com 8 e inserted falso, C-300 com 7 e verdadeiro. xmax é um detalhe interno, não uma API documentada, então trate isso como uma conveniência. A página de RETURNING cobre a cláusula por completo.
Erros que você vai encontrar
- "ON CONFLICT DO UPDATE command cannot affect row a second time": a mesma chave aparece duas vezes no
VALUESou noSELECTde um comando. Remova os duplicados antes, por exemplo comSELECT DISTINCT ON (sku) .... - "column reference is ambiguous": qualifique a coluna com o nome da tabela nas expressões do
SETe noWHERE, como acima. - Buracos nos ids identity ou serial: um insert que vira update já pegou um valor da sequence, então os ids pulam números. Isso é esperado.
O MERGE (PostgreSQL 15 em diante) também insere ou atualiza, mas não protege contra duas sessões inserindo a mesma chave ao mesmo tempo. Para upserts por chave, use ON CONFLICT. A referência oficial do INSERT lista todas as opções.
Perguntas frequentes
O PostgreSQL tem um comando UPSERT?
Não existe a palavra-chave UPSERT. O PostgreSQL 9.5 trouxe o INSERT ... ON CONFLICT, que insere uma linha ou, quando ela violaria uma restrição única, atualiza a linha existente (DO UPDATE) ou a ignora (DO NOTHING).
INSERT ... ON CONFLICT é seguro com inserts concorrentes?
Sim. Cada linha proposta termina num insert ou num update, mesmo quando outras sessões inserem a mesma chave no mesmo momento. Um SELECT seguido de INSERT ou UPDATE no código da aplicação não dá essa garantia.
Devo usar MERGE ou ON CONFLICT para um upsert?
Use ON CONFLICT para inserir ou atualizar por chave. O MERGE (PostgreSQL 15 em diante) cobre mais casos, como apagar as linhas que batem. Com inserts concorrentes da mesma chave ele pode falhar com violação de unicidade, o que o ON CONFLICT evita.
Por que recebo "there is no unique or exclusion constraint matching the ON CONFLICT specification"?
As colunas em ON CONFLICT (...) precisam bater exatamente com um índice ou restrição única. Adicione uma restrição UNIQUE ou um índice único nessas colunas, ou informe as colunas de um que já exista.