PostgreSQL normalmente permite múltiples valores NULL de SQL en una columna única. Para diagnosticar un error inesperado de clave duplicada, identifique el índice exacto, inspeccione los valores realmente enviados y verifique si su definición cambia cómo se comparan los valores NULL. Índices únicos

Esta guía está destinada a alguien que depura inserciones de artículos desde un Mac usando psql o node-postgres. Utiliza filas de artículos sintéticas y proporciona una secuencia de diagnóstico: reproducir el comportamiento predeterminado, rastrear la regla que falla y luego elegir la corrección apropiada.

¿Qué permite realmente una columna única nullable?

Una columna nullable puede contener NULL; una restricción única determina qué combinaciones pueden repetirse. Con UNIQUE (slug) ordinario, los slugs no nulos repetidos se rechazan, mientras que los valores NULL repetidos se permiten. Con UNIQUE (section, slug) ordinario, un valor NULL en cualquiera de las columnas restringidas permite combinaciones que de otro modo se repetirían bajo esa restricción. Otras reglas únicas en la tabla siguen aplicándose. Restricciones

PostgreSQL 15 introdujo NULLS NOT DISTINCT, que trata los valores NULL como iguales para la comprobación de unicidad. Para una clave compuesta, la combinación completa debe coincidir: ('notes', NULL) entra en conflicto con otro ('notes', NULL), pero no con ('guides', NULL). PostgreSQL 14 no soporta esa cláusula. Versión 15, Índices únicos

Regla o entrada real ¿Puede un valor repetido causar un error de clave duplicada?
UNIQUE (slug) ordinario con NULL de SQL No, bajo esta regla sola
UNIQUE NULLS NOT DISTINCT (slug) Sí, en PostgreSQL 15 o posterior
UNIQUE (slug) ordinario con '' Sí; esto es una cadena vacía
UNIQUE (slug) ordinario con 'NULL' Sí; esto es texto
Otra clave única en la misma fila Sí, independientemente del slug nullable

SQL NULL, la cadena vacía y el texto 'NULL' son entradas diferentes. La documentación de comparación de PostgreSQL distingue explícitamente NULL de los valores de texto ordinarios; compruebe la nulidad con IS NULL. Funciones y operadores de comparación

Reproduzca el comportamiento predeterminado en Terminal de Mac

Necesita una conexión existente a PostgreSQL y el comando psql disponible en Terminal. Usando la configuración de conexión que normalmente usa, abra una sesión. Por ejemplo, si su base de datos local se llama postgres, ejecute lo siguiente. La opción -d selecciona la base de datos. psql

psql -d postgres

Compruebe la versión del servidor conectado antes de elegir la sintaxis específica de versión. server_version informa la versión del servidor, que es el límite relevante para NULLS NOT DISTINCT. Opciones preestablecidas

SHOW server_version;

Ejecute este ejemplo de referencia completo una vez en una sesión nueva. Su restricción única ordinaria funciona en PostgreSQL 14 y posteriores; solo crea una tabla de demostración temporal.

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;

Las cuatro inserciones deberían tener éxito bajo la definición mostrada. Las dos primeras filas tienen is_sql_null = true; la tercera tiene is_empty_string = true; la cuarta contiene texto literal. Una comparación que involucra NULL puede devolver NULL, así que use la columna de nulidad explícita para interpretar los resultados. Restricciones, Funciones y operadores de comparación

Esta línea base le da un punto de división útil. Si la aplicación falla mientras este ejemplo tiene éxito, compare la definición real de la tabla de la aplicación y los valores enviados con la línea base. La presencia de una columna nullable por sí sola no identifica la regla que falla.

Encuentre la regla exacta detrás del error

Comience con SQLSTATE 23505, el código de PostgreSQL para unique_violation. Verifique el código en lugar de coincidir con el texto del mensaje de error. Luego registre el esquema, tabla, restricción y detalle reportados cuando estén disponibles. PostgreSQL trata un índice como una restricción para el campo constraint-name del error, incluso cuando se creó directamente con CREATE UNIQUE INDEX. Códigos de error de PostgreSQL, Campos de mensajes de error y aviso

En la misma sesión psql que la línea base, inspeccione los índices con esta consulta de diagnóstico. La vista pg_indexes expone el esquema, nombre y definición reconstruida de cada índice. Al adaptar la consulta, coincida tanto la tabla como el esquema reportados por el error. pg_indexes

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

Lea cada definición única, comenzando con el nombre del error. Un article_id duplicado viola la clave primaria incluso cuando slug es NULL. Las claves primarias requieren valores únicos y no nulos; una columna nullable en otra parte de la fila no relaja ese requisito. Restricciones

Busque tres cambios respecto a la línea base:

  • Igualdad de NULL: NULLS NOT DISTINCT hace deliberadamente que las claves NULL repetidas entren en conflicto.
  • Expresiones: la unicidad puede aplicarse a un valor calculado en lugar de a la columna almacenada.
  • Predicados: un índice parcial aplica su regla solo a las filas que satisfacen su condición WHERE.

Para un índice de expresión, reproduzca la expresión indexada al investigar el conflicto. Por ejemplo, un índice en lower(slug) puede rechazar dos slugs no nulos que difieren solo en mayúsculas/minúsculas. Un índice en COALESCE(slug, '') mapea tanto NULL como la cadena vacía al mismo valor indexado; en consecuencia, esas entradas pueden colisionar. Esto se deriva de la unicidad de índices de expresión y el comportamiento documentado de COALESCE. Índices en expresiones, Expresiones condicionales

Un índice único parcial también necesita una lectura cuidadosa. Un índice en section con WHERE slug IS NULL exige una fila coincidente por sección entre las filas con slugs NULL. No exige unicidad en slug mismo. PostgreSQL documenta los índices únicos parciales como que exigen unicidad dentro del subconjunto seleccionado por su predicado. Índices parciales

Compruebe qué envía realmente node-postgres

node-postgres convierte tanto null como undefined de JavaScript en parámetros de consulta a NULL de SQL. Las cadenas vacías y la cadena 'NULL' permanecen como entradas de texto ordinarias. También distinga un parámetro NULL suministrado de una columna SQL omitida: PostgreSQL rellena una columna omitida con su valor predeterminado declarado, o NULL cuando no existe predeterminado. Consultas, INSERT

El siguiente programa completo hace visibles esas diferencias. Requiere Node.js, npm y su configuración de conexión PostgreSQL existente. En una carpeta de trabajo, instale la dependencia, guarde el programa como diagnose-null.mjs y luego ejecútelo. El cliente usa variables de entorno estándar de conexión PostgreSQL. Conexión

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

Bajo el esquema de este programa, las dos primeras filas deberían almacenar NULL de SQL a pesar del valor predeterminado declarado. La primera cadena vacía y la primera cadena 'NULL' deberían insertarse; sus repeticiones deberían producir 23505. El programa informa esos conflictos esperados y vuelve a lanzar otros errores. Sus nombres de propiedades de diagnóstico coinciden con los campos DatabaseError de node-postgres. Consultas, Fuente DatabaseError

Los ejemplos de base de datos fueron inspeccionados contra la documentación recuperada, pero no se ejecutaron contra PostgreSQL durante la preparación.

Consulte claves nullable sin perder las filas coincidentes

Una búsqueda de diagnóstico usando slug = NULL no encontrará slugs NULL. Use IS NULL para una búsqueda fija de NULL, o IS NOT DISTINCT FROM cuando un valor puede ser NULL o no NULL. Este último trata dos NULL como iguales para la comparación. Funciones y operadores de comparación

Esta consulta de diagnóstico se ejecuta contra la tabla article_probe anterior en la misma sesión. Debería devolver las dos filas con slug NULL.

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

Para una búsqueda parametrizada, el fragmento de predicado correspondiente es WHERE slug IS NOT DISTINCT FROM $1::text. Haga coincidir cada componente de clave para una regla compuesta, y reproduzca cualquier expresión indexada o predicado de índice parcial. Encontrar filas NULL coincidentes establece qué se almacena; si violan la unicidad aún depende de la definición del índice.

Elija la corrección que coincida con la regla prevista

Si los artículos pueden tener cualquier número de slugs faltantes, UNIQUE (slug) ordinario ya expresa ese requisito. Corrija un valor de texto accidental, una expresión no intencional, o la otra clave duplicada identificada por el error. Cambiar el manejo de NULL no resolverá una colisión en una clave diferente.

Si el requisito es un slug NULL por sección, PostgreSQL 15 o posterior puede expresarlo directamente. Esta demostración independiente termina deliberadamente con una inserción que se espera produzca 23505: la primera fila ya ocupa esa combinación de sección-y-NULL. Versión 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');

En PostgreSQL 14, use índices únicos parciales para este diseño particular de dos columnas. El siguiente ejemplo independiente implementa la misma regla porque section es no NULL y slug es el único componente de clave nullable. Esta es una construcción derivada del comportamiento documentado de índices parciales de PostgreSQL. Índices parciales

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');

El primer índice evita slugs poblados repetidos dentro de una sección. El segundo permite un slug faltante por sección. Más componentes de clave nullable requieren consideración adicional de sus combinaciones posibles; esta construcción de dos índices es específica del esquema declarado.

Finalmente, elija el manejo de conflictos explícitamente. ON CONFLICT DO NOTHING omite una inserción en conflicto; no repara los valores subyacentes. ON CONFLICT DO UPDATE requiere un objetivo de conflicto, cuyas columnas, expresiones y predicado aplicable deben identificar la regla prevista. Ninguna acción crea un conflicto NULL donde la unicidad ordinaria permite NULL repetidos. INSERT

Un diagnóstico está completo cuando puede conectar el nombre del índice reportado a su definición, reproducir los valores de clave enviados y explicar la colisión bajo esa regla. Repita esa entrada exacta después de la corrección, comprobando tanto el resultado devuelto como las filas almacenadas para que el artículo previsto esté contabilizado.

Fuentes