postgresql - not - ¿Por qué Postgres maneja los NULL de manera inconsistente cuando se trata de restricciones únicas?

set null column postgresql

Question

Recientemente noté una inconsistencia en la forma en que Postgres maneja los NULL en columnas con una restricción única.

Considera una tabla de personas:

create table People (
   pid  int  not null,
   name text not null,
   SSN  text unique,
 primary key (pid)
);

La columna SSN debe mantenerse única. Podemos comprobar que:

-- Add a row.
insert into People(pid, name, SSN)
values(0, ''Bob'', ''123'');

-- Test the unique constraint.
insert into People(pid, name, SSN)
values(1, ''Carol'', ''123'');

La segunda inserción falla porque viola la restricción única en el SSN. Hasta ahora tan bueno. Pero probemos un NULL:

insert into People(pid, name, SSN)
values(1, ''Carol'', null);

Eso funciona.

select *    
from People;

0;"Bob";"123"
1;"Carol";"<NULL>"

Una columna única tendrá un valor nulo. Interesante. ¿Cómo puede Postgres afirmar que el nulo es de alguna manera único, o no es único?

Me pregunto si puedo agregar dos filas con nulo en una columna única.

insert into People(pid, name, SSN)
values(2, ''Ted'', null);

select *    
from People;

0;"Bob";"123"
1;"Carol";"<NULL>"
2;"Ted";"<NULL>"

Si puedo. Ahora hay dos filas con NULL en la columna SSN, aunque se supone que SSN es único.

La documentación de Postgres dice: Para el propósito de una restricción única, los valores nulos no se consideran iguales.

Bueno. Puedo ver el punto de esto. Es una buena sutileza en el manejo de nulos: al considerar que todos los valores nulos en una columna con restricciones únicas no están vinculados, demoramos la aplicación de restricciones únicas hasta que haya un valor real no nulo en el que basar esa aplicación.

Eso es muy bonito. Pero aquí es donde Postgres me pierde. Si todos los NULL en una columna restringida única no son iguales, como dice la documentación, entonces deberíamos ver todos los nulos en una consulta distinta seleccionada .

select distinct SSN
from People;

"<NULL>"
"123"

No Solo hay un nulo. Parece que Postgres tiene esto mal. Pero me pregunto: ¿hay otra explicación?

Editar:

Los documentos de Postgres sí especifican que "los valores nulos se consideran iguales en esta comparación". en la sección de SELECCIONAR DISTINTO . Si bien no entiendo esa idea, me alegro de que esté explicada en los documentos.

Answer 1

Casi siempre es un error cuando se trata de null decir:

"los nulos se comportan como tal aquí, * por lo que deben comportarse como tal aquí"

Here hay un excelente ensayo sobre el tema desde una perspectiva postgres. Resumiendo brevemente, los nulos se tratan de manera diferente según el contexto y no cometen el error de hacer suposiciones sobre ellos.

Answer 2

La conclusión es que PostgreSQL hace lo que hace con nulos porque el estándar de SQL así lo dice.

Los nulos son obviamente difíciles y pueden interpretarse de múltiples maneras (valor desconocido, valor ausente, etc.), por lo que cuando el estándar SQL se escribió inicialmente, los autores tuvieron que hacer algunas llamadas en ciertos lugares. Yo diría que el tiempo ha demostrado que tienen más o menos razón, pero eso no significa que no pueda haber otro lenguaje de base de datos que maneje los valores desconocidos y ausentes de forma ligeramente (o salvajemente) diferente. Pero PostgreSQL implementa SQL, así que eso es todo.

Como ya se mencionó en una respuesta diferente, Jeff Davis ha escrito algunos buenos artículos y presentaciones sobre cómo lidiar con los nulos.

Answer 3

Múltiples valores NULL en un índice único están bien porque x = NULL es falso para todo x y, en particular, cuando x es NULL. También se encontrará con este comportamiento en las cláusulas WHERE en las que tiene que decir WHERE x IS NULL y WHERE x IS NOT NULL lugar de WHERE x = NULL y WHERE x <> NULL .

Answer 4

NULL se considera único porque NULL no representa la ausencia de un valor. Un valor NULL en una columna es un valor desconocido. Cuando comparas dos incógnitas, no sabes si son iguales o no porque no sabes lo que son.

Imagina que tienes dos casillas marcadas con A y B. Si no abres las casillas y no puedes ver dentro, nunca sabes qué contenido tienen. Si se le pregunta "¿Son iguales los contenidos de estos dos cuadros?" Sólo puedes responder "No sé".

En este caso, PostgreSQL hará lo mismo. Cuando se le pide que compare dos NULL s, dice "No sé". Esto tiene mucho que ver con la semántica loca de NULL en las bases de datos SQL. El artículo vinculado a @JackPDouglas es un excelente punto de partida para comprender cómo se comportan los NULL s. Sólo ten cuidado: varía según el proveedor.