Normalmente, o PostgreSQL permite vários valores SQL NULL em uma coluna única. Para diagnosticar um erro inesperado de chave duplicada, identifique o índice exato, inspecione os valores realmente enviados e verifique se a definição altera a forma como os valores nulos são comparados. Índices únicos

Este passo a passo é para quem está depurando inserções de artigos em um Mac usando psql ou node-postgres. Ele usa linhas de artigos sintéticas e apresenta uma sequência de diagnóstico: reproduza o comportamento padrão, rastreie a regra que falhou e, em seguida, escolha a correção apropriada.

O que uma coluna única anulável realmente permite?

Uma coluna anulável pode conter NULL; uma restrição única determina quais combinações podem se repetir. Com UNIQUE (slug) comum, slugs não nulos repetidos são rejeitados, enquanto valores nulos repetidos são permitidos. Com UNIQUE (section, slug) comum, um valor nulo em qualquer uma das colunas restritas permite combinações que, de outro modo, seriam repetidas sob essa restrição. As demais regras únicas da tabela continuam valendo. Restrições

O PostgreSQL 15 introduziu NULLS NOT DISTINCT, que trata valores nulos como iguais ao verificar a unicidade. Para uma chave composta, a combinação inteira precisa corresponder: ('notes', NULL) entra em conflito com outro ('notes', NULL), mas não com ('guides', NULL). O PostgreSQL 14 não oferece suporte a essa cláusula. Versão 15, Índices únicos

Regra ou entrada efetiva Um valor repetido pode causar erro de chave duplicada?
UNIQUE (slug) comum com SQL NULL Não, considerando apenas essa regra
UNIQUE NULLS NOT DISTINCT (slug) Sim, no PostgreSQL 15 ou posterior
UNIQUE (slug) comum com '' Sim; esta é uma string vazia
UNIQUE (slug) comum com 'NULL' Sim; isto é texto
Outra chave única na mesma linha Sim, independentemente do slug anulável

SQL NULL, a string vazia e o texto 'NULL' são entradas diferentes. A documentação de comparação do PostgreSQL distingue explicitamente valores nulos de valores de texto comuns; teste se um valor é nulo com IS NULL. Funções e operadores de comparação

Reproduza o comportamento padrão no Terminal do Mac

Você precisa de uma conexão existente com o PostgreSQL e do comando psql disponível no Terminal. Use as configurações de conexão que costuma usar para abrir uma sessão. Por exemplo, se seu banco de dados local se chamar postgres, execute o seguinte. A opção -d seleciona o banco de dados. psql

psql -d postgres

Confira a versão do servidor conectado antes de escolher sintaxe específica de uma versão. server_version informa a versão do servidor, o limite relevante para NULLS NOT DISTINCT. Opções predefinidas

SHOW server_version;

Execute este exemplo de base completo uma vez em uma sessão nova. Sua restrição única comum funciona no PostgreSQL 14 e posterior; ele cria apenas uma tabela temporária de demonstração.

CREATE TEMP TABLE article_probe (
    article_id integer PRIMARY KEY,
    title text NOT NULL,
    slug text,
    CONSTRAINT article_probe_slug_key UNIQUE (slug)
);

INSERT INTO article_probe (article_id, title, slug)
VALUES
    (1, 'Paper shapes', NULL),
    (2, 'Color arrangements', NULL),
    (3, 'Desk sketches', ''),
    (4, 'Pattern notes', 'NULL');

SELECT article_id, title, slug,
       slug IS NULL AS is_sql_null,
       slug = '' AS is_empty_string
FROM article_probe
ORDER BY article_id;

As quatro inserções devem ser bem-sucedidas com a definição exibida. As duas primeiras linhas têm is_sql_null = true; a terceira tem is_empty_string = true; a quarta contém texto literal. Uma comparação que envolva um valor nulo pode retornar nulo; por isso, use a coluna explícita de nulidade para interpretar os resultados. Restrições, Funções e operadores de comparação

Esta linha de base oferece um critério útil para separar os casos. Se a aplicação falhar enquanto este exemplo for bem-sucedido, compare a definição real da tabela na aplicação e os valores enviados com a linha de base. A simples presença de uma coluna anulável não identifica a regra que falhou.

Encontre a regra exata por trás do erro

Comece pelo SQLSTATE 23505, o código do PostgreSQL para unique_violation. Confira o código em vez de comparar o texto da mensagem de erro. Em seguida, registre o esquema, a tabela, a restrição e os detalhes informados, quando disponíveis. O PostgreSQL trata um índice como uma restrição no campo do nome da restrição do erro, mesmo quando ele foi criado diretamente com CREATE UNIQUE INDEX. Códigos de erro do PostgreSQL, Campos de mensagens de erro e aviso

Na mesma sessão psql usada para a linha de base, inspecione os índices com esta consulta de diagnóstico. A visão pg_indexes expõe o esquema, o nome e a definição reconstruída de cada índice. Ao adaptar a consulta, filtre pela tabela e pelo esquema informados no erro. pg_indexes

SELECT schemaname, tablename, indexname, indexdef
FROM pg_indexes
WHERE tablename = 'article_probe'
ORDER BY schemaname, indexname;

Leia todas as definições únicas, começando pelo nome indicado no erro. Uma chave primária article_id viola a restrição mesmo quando slug é nulo. Chaves primárias exigem valores únicos e não nulos; uma coluna anulável em outra parte da linha não flexibiliza essa exigência. Restrições

Procure três diferenças em relação à linha de base:

  • Igualdade de valores nulos: NULLS NOT DISTINCT faz com que chaves nulas repetidas entrem em conflito intencionalmente.
  • Expressões: a unicidade pode se aplicar a um valor calculado, em vez de à coluna armazenada.
  • Predicados: um índice parcial aplica sua regra somente às linhas que satisfazem sua condição WHERE.

Para um índice de expressão, reproduza a expressão indexada ao investigar o conflito. Por exemplo, um índice em lower(slug) pode rejeitar dois slugs não nulos que diferem apenas entre maiúsculas e minúsculas. Um índice em COALESCE(slug, '') mapeia tanto valores nulos quanto a string vazia para o mesmo valor indexado; consequentemente, essas entradas podem colidir. Isso decorre da unicidade em índices de expressão e do comportamento documentado de COALESCE. Índices em expressões, Expressões condicionais

Um índice único parcial também exige atenção. Um índice em section com WHERE slug IS NULL impõe que haja apenas uma linha correspondente por seção entre as linhas com slugs nulos. Ele não impõe unicidade sobre slug em si. O PostgreSQL documenta que índices únicos parciais impõem unicidade dentro do subconjunto selecionado pelo predicado. Índices parciais

Confira o que o node-postgres realmente envia

O node-postgres converte tanto parâmetros de consulta JavaScript null quanto undefined em valores SQL nulos. Strings vazias e a string 'NULL' permanecem entradas de texto comuns. Diferencie também um parâmetro nulo fornecido de uma coluna SQL omitida: o PostgreSQL preenche uma coluna omitida com o valor padrão declarado ou com nulo quando não há valor padrão. Consultas, INSERT

O programa completo a seguir torna visíveis essas diferenças. Ele requer Node.js, npm e as configurações existentes da sua conexão com o PostgreSQL. Em uma pasta de trabalho, instale a dependência, salve o programa como diagnose-null.mjs e, em seguida, execute-o. O cliente usa as variáveis de ambiente padrão de conexão do PostgreSQL. Conexão

npm install pg

import pg from 'pg';

const { Client } = pg;
const client = new Client();

async function main() {
  await client.connect();
  try {
    await client.query(`
      CREATE TEMP TABLE article_node_probe (
        title text NOT NULL,
        slug text DEFAULT 'untitled',
        CONSTRAINT article_node_probe_slug_key UNIQUE (slug)
      )
    `);

    const samples = [
      { title: 'Paper shapes', slug: null },
      { title: 'Color arrangements', slug: undefined },
      { title: 'Desk sketches', slug: '' },
      { title: 'More sketches', slug: '' },
      { title: 'Pattern notes', slug: 'NULL' },
      { title: 'More patterns', slug: 'NULL' },
    ];

    for (const sample of samples) {
      console.log('Input:', {
        title: sample.title,
        type: typeof sample.slug,
        value: sample.slug,
      });

      try {
        const result = await client.query(
          `INSERT INTO article_node_probe (title, slug)
           VALUES ($1, $2)
           RETURNING title, slug, slug IS NULL AS is_sql_null`,
          [sample.title, sample.slug],
        );
        console.log('Inserted:', result.rows[0]);
      } catch (error) {
        if (error.code !== '23505') throw error;
        console.log('Duplicate:', {
          code: error.code,
          schema: error.schema,
          table: error.table,
          constraint: error.constraint,
          detail: error.detail,
        });
      }
    }
  } finally {
    await client.end();
  }
}

main().catch((error) => {
  console.error(error);
  process.exitCode = 1;
});

PGDATABASE=postgres node diagnose-null.mjs

Com o esquema deste programa, as duas primeiras linhas devem armazenar valores SQL nulos, apesar do valor padrão declarado. A primeira string vazia e a primeira string 'NULL' devem ser inseridas; as repetições devem gerar 23505. O programa informa esses conflitos esperados e propaga novamente os demais erros. Os nomes das propriedades de diagnóstico correspondem aos campos DatabaseError do node-postgres. Consultas, Código-fonte de DatabaseError

Os exemplos de banco de dados foram conferidos com a documentação consultada, mas não foram executados no PostgreSQL durante a preparação.

Consulte chaves anuláveis sem deixar de encontrar as linhas correspondentes

Uma consulta de diagnóstico com slug = NULL não encontrará slugs nulos. Use IS NULL para uma busca fixa por valores nulos ou IS NOT DISTINCT FROM quando o valor puder ser nulo ou não nulo. Este último trata dois valores nulos como iguais na comparação. Funções e operadores de comparação

Esta consulta de diagnóstico é executada na tabela article_probe criada anteriormente, na mesma sessão. Ela deve retornar as duas linhas com slug nulo.

SELECT article_id, title, slug
FROM article_probe
WHERE slug IS NOT DISTINCT FROM NULL::text
ORDER BY article_id;

Para uma consulta parametrizada, o fragmento de predicado correspondente é WHERE slug IS NOT DISTINCT FROM $1::text. Corresponda a todos os componentes da chave para uma regra composta e reproduza qualquer expressão indexada ou predicado de índice parcial. Encontrar linhas correspondentes com valores nulos confirma o que está armazenado; se elas violam a unicidade ainda depende da definição do índice.

Escolha a correção que corresponde à regra desejada

Se os artigos puderem ter qualquer quantidade de slugs ausentes, UNIQUE (slug) comum já expressa esse requisito. Corrija um valor de texto acidental, uma expressão não intencional ou a outra chave duplicada identificada pelo erro. Alterar o tratamento de valores nulos não resolverá uma colisão em outra chave.

Se o requisito for um slug nulo por seção, o PostgreSQL 15 ou posterior pode expressá-lo diretamente. Esta demonstração independente termina intencionalmente com uma inserção que deve gerar 23505: a primeira linha já ocupa essa combinação de seção e valor nulo. Versão 15, Índices únicos

CREATE TEMP TABLE article_one_missing (
    section text NOT NULL,
    slug text,
    title text NOT NULL,
    CONSTRAINT article_one_missing_key
        UNIQUE NULLS NOT DISTINCT (section, slug)
);

INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_one_missing_key.
INSERT INTO article_one_missing (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

No PostgreSQL 14, use índices únicos parciais para este projeto específico de duas colunas. O exemplo independente a seguir implementa a mesma regra porque section não é nulo e slug é o único componente anulável da chave. Esta construção deriva do comportamento documentado dos índices parciais do PostgreSQL. Índices parciais

CREATE TEMP TABLE article_legacy (
    section text NOT NULL,
    slug text,
    title text NOT NULL
);

CREATE UNIQUE INDEX article_legacy_present_key
ON article_legacy (section, slug)
WHERE slug IS NOT NULL;

CREATE UNIQUE INDEX article_legacy_missing_key
ON article_legacy (section)
WHERE slug IS NULL;

INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Paper shapes');

-- Expected: 23505 on article_legacy_missing_key.
INSERT INTO article_legacy (section, slug, title)
VALUES ('notes', NULL, 'Color arrangements');

O primeiro índice impede a repetição de slugs preenchidos dentro de uma seção. O segundo permite um slug ausente por seção. Mais componentes anuláveis na chave exigem considerar suas combinações possíveis; esta construção com dois índices é específica para o esquema indicado.

Por fim, escolha explicitamente o tratamento de conflitos. ON CONFLICT DO NOTHING ignora uma inserção conflitante; não corrige os valores subjacentes. ON CONFLICT DO UPDATE exige um alvo de conflito cujas colunas, expressões e predicado aplicável identifiquem a regra desejada. Nenhuma dessas ações cria um conflito de valores nulos quando a unicidade comum permite que eles se repitam. INSERT

O diagnóstico está concluído quando você consegue relacionar o nome do índice informado à sua definição, reproduzir os valores enviados para a chave e explicar a colisão segundo essa regra. Após a correção, repita exatamente essa entrada e confira tanto o resultado retornado quanto as linhas armazenadas para garantir que o artigo desejado foi contabilizado.

Fontes