O PostgreSQL não pode usar ON CONFLICT DO UPDATE como uma operação genérica de “corresponder a qualquer uma das chaves únicas”. Escolha uma chave determinante ou verifique explicitamente ambas as chaves em uma transação e rejeite entradas que identifiquem duas linhas diferentes.

Este tutorial ajuda um desenvolvedor que trabalha no Terminal em um Mac a implementar essa segunda política com node-postgres: inserir quando nenhuma das chaves corresponder, atualizar quando exatamente um artigo corresponder e retornar um conflito explícito quando as chaves apontarem para artigos diferentes. Todas as linhas são sintéticas.

Os exemplos de SQL têm como alvo o PostgreSQL 18, a versão fornecida pela documentação atual consultada. A implementação usa uma tabela comum, não particionada, com duas restrições de unicidade imediatas. Confira a versão principal do seu servidor antes de adaptar exemplos do manual de outra versão. Documentação do PostgreSQL 18 sobre INSERT

Por que duas restrições de unicidade não podem se tornar um único alvo de conflito

Suponha que um artigo tenha uma referência externa e um slug. Cada um deve ser único independentemente. Comece com esta configuração completa em um banco de dados de demonstração vazio; salve-a como setup.sql.

CREATE TABLE articles (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    external_key text NOT NULL,
    slug text NOT NULL,
    title text NOT NULL,
    CONSTRAINT articles_external_key_key UNIQUE (external_key),
    CONSTRAINT articles_slug_key UNIQUE (slug)
);

INSERT INTO articles (external_key, slug, title)
VALUES
    ('feed-101', 'blue-notes', 'Blue notes'),
    ('feed-202', 'green-notes', 'Green notes');

Essas declarações impõem duas regras separadas. Substituí-las por UNIQUE (external_key, slug) mudaria o modelo: apenas o par precisaria ser único, permitindo chaves externas repetidas e slugs repetidos em combinações diferentes. Manter ambas as colunas como NOT NULL também evita introduzir o comportamento de chaves nulas neste exemplo. Restrições do PostgreSQL

Para DO UPDATE, o PostgreSQL exige um alvo de conflito. Uma lista de colunas descreve as colunas de um índice único correspondente; ela não significa “qualquer uma das colunas”. Portanto, ON CONFLICT (external_key, slug) não pode inferir um árbitro a partir das duas restrições separadas acima. Nomear uma restrição seleciona essa restrição, e a sintaxe fornece uma cláusula ON CONFLICT, em vez de uma cadeia de manipuladores alternativos. PostgreSQL INSERT: cláusula ON CONFLICT

O problema mais profundo é decidir qual artigo existente a linha recebida representa. Considere os valores recebidos feed-101 e green-notes: a chave externa pertence ao artigo azul, enquanto o slug pertence ao artigo verde. Atualizar qualquer uma das linhas com ambas as chaves recebidas entraria em conflito com a outra linha. Escolher a primeira correspondência ocultaria uma decisão ainda não resolvida sobre a entrada.

Decida o que deve acontecer para cada combinação de correspondências

Use a seguinte tabela de decisão antes de escrever a consulta. Ela propõe uma política da aplicação derivada deste esquema, e não um comportamento fornecido automaticamente pelo PostgreSQL.

Chave externa recebida Slug recebido Linhas correspondentes distintas Ação neste tutorial
Não utilizada Não utilizado 0 Inserir um artigo
Corresponde a um artigo Não utilizado 1 Atualizar esse artigo, incluindo o slug
Não utilizada Corresponde a um artigo 1 Atualizar esse artigo, incluindo a chave externa
Corresponde ao artigo A Corresponde ao artigo A 1 Atualizar o artigo A
Corresponde ao artigo A Corresponde ao artigo B 2 Rejeitar a entrada; preservar ambos os artigos

Os casos com uma correspondência permitem deliberadamente que qualquer chave identifique um artigo e que a outra chave seja alterada. Confirme se esse comportamento atende às necessidades da sua importação. Se uma chave externa nunca puder mudar, o caso de correspondência apenas pelo slug deverá se tornar um conflito da aplicação.

Se sua regra real for mais simples — “a chave externa sempre determina o artigo” — use um upsert normal com alvo especificado. Esta instrução completa é executada depois da configuração acima:

INSERT INTO articles (external_key, slug, title)
VALUES ('feed-101', 'blue-notes', 'Revised blue notes')
ON CONFLICT (external_key) DO UPDATE
SET slug = EXCLUDED.slug,
    title = EXCLUDED.title
RETURNING id, external_key, slug, title;

Aqui, EXCLUDED fornece os valores propostos. A outra restrição de unicidade continua sendo aplicada: alterar o slug proposto para green-notes gera um conflito de unicidade independente, em vez de redirecionar a operação para o outro artigo. A garantia atômica de inserção ou atualização do upsert com alvo especificado depende da ausência de erros independentes. Documentação do PostgreSQL sobre INSERT

Mantenha estável a decisão baseada em qualquer chave durante gravações simultâneas

Uma consulta seguida por uma inserção cria um intervalo entre a decisão e a gravação. Outra transação poderia inserir uma chave correspondente durante esse intervalo. Adicionar FOR UPDATE à consulta bloqueia as linhas recuperadas; quando nenhuma linha corresponde, não há linha recuperada para bloquear. Por isso, o bloqueio de linhas, por si só, é insuficiente nesse caso específico de nenhuma correspondência. Bloqueio explícito no PostgreSQL

Para uma importação pequena ou uma operação ocasional no desktop, uma solução direta é adquirir um bloqueio SHARE ROW EXCLUSIVE na tabela antes de verificar qualquer uma das chaves. Esse modo entra em conflito com o bloqueio ROW EXCLUSIVE adquirido por inserções, atualizações e exclusões comuns, bem como com outra instância dele próprio. Leituras comuns continuam compatíveis. Modos de bloqueio de tabela do PostgreSQL

A sequência resultante é: iniciar a transação, adquirir o bloqueio da tabela, encontrar as linhas correspondentes distintas, aplicar a tabela de decisão e confirmar a transação. O bloqueio explícito aguarda bloqueios incompatíveis e permanece ativo até o fim da transação. PostgreSQL LOCK

Use READ COMMITTED explicitamente aqui. A consulta vê os dados confirmados antes de começar, inclusive as gravações concluídas enquanto se aguardava a aquisição do bloqueio. A combinação desse comportamento de snapshot com o bloqueio da tabela adquirido fornece uma base estável para decidir e gravar nesta implementação. Isolamento de transações no PostgreSQL

Este é um bloqueio amplo: gravações de artigos sem relação com a operação também aguardam. Mantenha a transação curta, sem prompts interativos ou trabalho externo entre o bloqueio e a confirmação. Para importações simultâneas frequentes, reavalie se uma única chave canônica pode definir a identidade antes de adotar este projeto.

Implemente a transação com node-postgres

Você precisa de um banco de dados PostgreSQL 18 de demonstração em execução, do comando psql e de uma versão do Node.js compatível com o pacote node-postgres instalado. No Terminal, instale o pacote e carregue o arquivo de configuração. O primeiro comando cria os metadados do pacote se o diretório de demonstração ainda não os tiver. Instalação e compatibilidade do node-postgres

npm init -y
npm install pg
psql -v ON_ERROR_STOP=1 -f setup.sql

Esses comandos pressupõem que as configurações normais da sua conexão local já selecionem o banco de dados de demonstração. O programa usa new Pool(), que lê as mesmas variáveis de ambiente de conexão do PostgreSQL usadas por psql; defina PGDATABASE como o nome do seu banco de dados de demonstração, se necessário. Conexão com node-postgres

Salve este programa completo como upsert-articles.mjs. Ele pressupõe que a tabela configurada não tenha gatilhos ou regras adicionais que alterem o comportamento de correspondência ou atualização. Os valores usam parâmetros posicionais fornecidos separadamente do texto SQL. Consultas com node-postgres

import pg from 'pg';
const { Pool } = pg;
const pool = new Pool();

async function upsertArticle(input) {
  const client = await pool.connect();
  let discardClient = false;

  try {
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED');
    await client.query(
      'LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE'
    );

    const matches = await client.query(
      `SELECT id FROM articles
       WHERE external_key = $1 OR slug = $2
       ORDER BY id`,
      [input.externalKey, input.slug]
    );

    if (matches.rows.length > 1) {
      const error = new Error(
        'Incoming keys identify two different articles'
      );
      error.code = 'ARTICLE_KEY_CONFLICT';
      throw error;
    }

    const values = [input.externalKey, input.slug, input.title];
    const result = matches.rows.length === 0
      ? await client.query(
          `INSERT INTO articles (external_key, slug, title)
           VALUES ($1, $2, $3)
           RETURNING id, external_key, slug, title`,
          values
        )
      : await client.query(
          `UPDATE articles
           SET external_key = $1, slug = $2, title = $3
           WHERE id = $4
           RETURNING id, external_key, slug, title`,
          [...values, matches.rows[0].id]
        );

    if (result.rows.length !== 1) {
      throw new Error('Expected exactly one article');
    }

    await client.query('COMMIT');
    return result.rows[0];
  } catch (error) {
    try {
      await client.query('ROLLBACK');
    } catch {
      discardClient = true;
    }
    throw error;
  } finally {
    client.release(discardClient);
  }
}

try {
  console.log(await upsertArticle({
    externalKey: 'feed-101',
    slug: 'blue-notes',
    title: 'Revised blue notes'
  }));
} finally {
  await pool.end();
}

Todas as instruções da transação usam o mesmo cliente obtido do pool, conforme exigido pelo node-postgres. Enviar essas instruções por chamadas separadas a pool.query() não preservaria essa exigência de conexão. Transações com node-postgres

O bloco finally sempre libera o cliente obtido. Se a reversão também falhar, release(true) descarta a conexão. O pool.end() externo fecha o pool quando o script de desktop termina. API do pool do node-postgres

Execute o programa com node upsert-articles.mjs. O resultado esperado é o artigo azul com o título revisado e as chaves inalteradas. A sintaxe JavaScript foi verificada com o Node.js; os exemplos de banco de dados foram conferidos com a documentação, mas não foram executados em um servidor PostgreSQL aqui.

Confira cada ramificação e trate as falhas explicitamente

Use as linhas da configuração e altere o objeto passado para upsertArticle() a fim de testar os casos a seguir. Execute cada caso usando o estado original da configuração ou leve em conta as alterações feitas pelos casos anteriores.

externalKey slug Resultado esperado
feed-303 amber-notes Inserir um novo artigo
feed-101 blue-revised Atualizar o artigo azul e substituir seu slug
feed-303 blue-notes Atualizar o artigo azul e substituir sua chave externa
feed-101 blue-notes Atualizar o título do artigo azul
feed-101 green-notes Lançar ARTICLE_KEY_CONFLICT; não alterar nenhum dos artigos

A consulta retorna linhas, portanto um artigo que corresponda às duas condições é contado uma única vez. Com essas duas chaves únicas independentes e não nulas, no máximo duas linhas distintas podem corresponder. O código nunca resolve um resultado com duas linhas usando LIMIT 1, excluindo um artigo ou ignorando silenciosamente a entrada.

ARTICLE_KEY_CONFLICT é um código definido pela aplicação. No PostgreSQL, 23505 significa unique_violation; é uma condição separada. Se esta última ocorrer depois de adaptar o exemplo, verifique a restrição violada e qualquer comportamento adicional da tabela, em vez de tratar toda duplicidade como o mesmo resultado. O PostgreSQL recomenda verificar códigos de erro em vez de analisar o texto das mensagens. Códigos de erro do PostgreSQL

A aquisição do bloqueio pode demorar. Se preferir falha imediata nesta tarefa de desktop, substitua a instrução de bloqueio por LOCK TABLE articles IN SHARE ROW EXCLUSIVE MODE NOWAIT. O PostgreSQL então gera um erro quando não consegue adquirir o bloqueio imediatamente, e o programa segue o caminho de reversão. PostgreSQL LOCK

DO NOTHING ou MERGE simplificariam a tarefa?

ON CONFLICT DO NOTHING sem alvo pode ignorar uma inserção que entre em conflito com qualquer restrição ou índice único utilizável. Escolha essa opção quando o resultado desejado for ignorar; ela não implementa a política de atualização desenvolvida aqui. Documentação do PostgreSQL sobre INSERT

MERGE foi introduzido no PostgreSQL 15. Embora permita uma condição de junção que envolva qualquer uma das chaves, essa junção ainda precisa de uma política para o caso em que uma linha de origem corresponda a dois artigos de destino diferentes. As ações continuam sujeitas às restrições comuns, e o comportamento em situações simultâneas não é intercambiável com o de um upsert com alvo especificado. Notas de versão do PostgreSQL 15, PostgreSQL MERGE

Para a tarefa que aceita qualquer chave, use a contagem de correspondências como decisão explícita: zero insere, uma atualiza e duas interrompem a operação com um conflito útil. Mantenha as restrições de unicidade e escolha uma estratégia de concorrência que preserve essa decisão até a conclusão da gravação. A implementação com bloqueio de tabela é um ponto de partida concreto quando é aceitável serializar as gravações de artigos.

Fontes