Почему Postgres обрабатывают NULL непоследовательно, когда задействованы уникальные ограничения?

Недавно я заметил несогласованность в том, как Postgres обрабатывает NULL в столбцах с уникальным ограничением.

Рассмотрим таблицу людей:

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

Столбец SSN должен быть уникальным. Мы можем проверить, что:

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

Вторая вставка терпит неудачу, потому что она нарушает единственное ограничение на SSN. Все идет нормально. Но пусть попробует NULL:

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

Это работает.

select *    
from People;

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

Уникальный столбец примет значение null. Интересно. Как Postgres утверждают, что нуль никоим образом не уникален или не уникален в этом отношении?

Интересно, могу ли я добавить две строки с нулевым значением в уникальный столбец.

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

select *    
from People;

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

Да, я могу. Теперь в столбце SSN есть две строки с NULL, хотя SSN должен быть уникальным.

Документация Postgres говорит: Для целей уникального ограничения нулевые значения не считаются равными.

Хорошо. Я вижу это. Это хорошая тонкость в обработке с нулевыми значениями: считая, что все NULL в столбце с уникальным ограничением являются непересекающимися, мы откладываем уникальное принудительное выполнение ограничений до тех пор, пока не будет фактическое ненулевое значение, на котором основывается это принудительное выполнение.

Это довольно круто. Но здесь, где Postgres теряет меня. Если все NULL в столбце с уникальным ограничением не равны, как говорит документация, тогда мы должны увидеть все нули в выбранном отдельном запросе.

select distinct SSN
from People;

"<NULL>"
"123"

Неа. Там только один пустой. Похоже, Postgres это неправильно. Но мне интересно: есть ли еще одно объяснение?

Изменить:

В документах Postgres указано, что "значения Null считаются равными в этом сравнении". в разделе на SELECT DISTINCT. Хотя я не понимаю этого понятия, я рад, что это указано в документах.

Ответ 1

Почти всегда ошибка при использовании null:

"nulls ведут себя так же, как и здесь, *, поэтому они должны вести себя как такой-то здесь"

Здесь - отличное эссе по этому вопросу с точки зрения postgres. Коротко подытоживая высказывание нулей, обрабатываются по-разному в зависимости от контекста и не ошибаются при принятии каких-либо предположений о них.

Ответ 2

Суть в том, что PostgreSQL делает то, что он делает с нулями, потому что стандарт SQL говорит об этом.

Нули, очевидно, сложны и могут быть интерпретированы несколькими способами (неизвестное значение, отсутствующее значение и т.д.), поэтому, когда стандарт SQL был первоначально написан, авторам пришлось совершать некоторые вызовы в определенных местах. Я бы сказал, что время доказало их более-менее право, но это не означает, что не может быть другого языка базы данных, который обрабатывает неизвестные и отсутствующие значения немного (или дико) по-разному. Но PostgreSQL реализует SQL, так что.

Как уже упоминалось в другом ответе, Джефф Дэвис написал несколько хороших статей и презентаций по работе с нулями.

Ответ 3

NULL считается уникальным, поскольку NULL не представляет собой отсутствие значения. A NULL в столбце - неизвестное значение. Когда вы сравниваете два неизвестных, вы не знаете, равны они или нет, потому что вы не знаете, что они собой представляют.

Представьте, что у вас есть две коробки с надписью A и B. Если вы не открываете ящики, и вы не видите внутри, вы никогда не знаете, что такое содержимое. Если вас спросят: "Является ли содержимое этих двух ящиков одинаковым?" вы можете ответить только "Я не знаю".

В этом случае PostgreSQL будет делать то же самое. Когда его попросят сравнить два NULL s, он говорит: "Я не знаю". Это имеет много общего с сумасшедшей семантикой вокруг NULL в SQL-базах данных. Статья @JackPDouglas, связанная с ней, является отличной отправной точкой для понимания поведения NULL. Просто будьте осторожны: это зависит от поставщика.

Ответ 4

Несколько значений NULL в уникальном индексе хорошо, потому что x = NULL является ложным для всех x и, в частности, когда x сам является NULL. Вы также столкнетесь с этим поведением в предложениях WHERE, где вы должны сказать WHERE x IS NULL и WHERE x IS NOT NULL, а не WHERE x = NULL и WHERE x <> NULL.