Почему postgres не считает null уникальным значением?

Question

[plx49638]

Создал таблицу с составным уникальным значением



Если в таблице есть запись
name = "имя"
region_id = 1
country_id = 1

то бд уже не разрешит повторно записать строчку с такими данными. Но проблема в том, что region_id может принимать значение null

Например так все нормально определяется (такая запись уже есть и второй раз уже такое значение не пропускает )



Так тоже нормально может создать значения потому что повторения нет



Но вот так уже не должен сохранять



То есть он считает два null как разные значения. подскажите как быть в такой ситуации ?

Comments

[Дмитрий]

Потому что null это неопределенность - а неопределенность бывает разная, поэтому сравнения where field = null не работают. И есть специальные функции is null и прочие

Answer 1

[mayton2019]

Если смотреть на проблему с точки зрения теории множеств - то использовать null - это так себе идея.
Ведь в данном контексте пустое поле входит в состав ключевых. Атрибуты - да. Могут быть пустыми и не
заполненными пользователем. Но ключи с пустотами вызывают еще больше вопросов если у вас
будет задача ссылочной целосности например.

Comments

[plx49638]

Данные приходят из сервиса которые для некоторых городов не показывает регионы. Поэтому на момент добавления не возможно сразу определить регионы для некоторых городов, но город добавить нужно.

Если смотреть на проблему с точки зрения теории множеств - то использовать null

А что бы вы использовали заместо null в таком случае ? или вообще не добавляли бы строку ? Или в таблице регионов сделали бы "Регион пустышку к которому присваивались бы такие города"?

[mayton2019]

plx49638, да. Завести район с названием UKNOWN - это нормальное решение для вашего кейса.

Answer 2

[Антон Антон]

Это фича (и не только postgres). Поступить - поменять индекс, сделав его не UNIQUE, а UNIQUE NULLS NOT DISTINCT
(и да, эта фича доступна только в постгре 15. для более ранних версий нужно хранить в этом поле не NULL, а, например, 0 (но тут есть свои нюансы с foreign keys))

Comments

[plx49638]

спасибо большое

[Akina]

для более ранних версий нужно хранить в этом поле не NULL, а, например, 0

Зачем ноль-то? Функциональные индексы вроде никто не отменял...

[Антон Антон]

Akina, типа

CREATE UNIQUE INDEX region_id_unique
  ON web_places
  USING btree(COALESCE(region_id, 0));

?

[Akina]

Антон Антон, Ну да, именно так. Если ноль - легитимное значение, то нужно заменить его на допустимое типом данных, но не допустимое логикой системы.