Нулевое значение не является уникальным

Ответ 1

Документация Postgres утверждает, что это поведение соответствует стандарту SQL:

В общем случае уникальное ограничение нарушается, когда есть два или более строки в таблице, где значения всех столбцов, включенных в ограничение равно. Однако два нулевых значения не учитываются равным в этом сравнении. Это означает, что даже при наличии уникального ограничение можно хранить повторяющиеся строки, которые содержат нулевой значение по меньшей мере в одном из ограниченных столбцов. Это поведение соответствует стандарту SQL [.]

Одна из возможностей - переосмыслить вашу схему (честно говоря, ограничение уникальности на name+address+college не имеет большого смысла в вашем примере).

Ответ 2

Если вам нужны только уникальные записи в результате запроса, используйте SELECT DISTINCT

 
postgres=# SELECT * FROM test;
 name | address | college 
------+---------+---------
 john | rome    | 
 john | rome    | 
 max  | tokyo   | 
(3 rows)

postgres=# SELECT DISTINCT * FROM test;
 name | address | college 
------+---------+---------
 john | rome    | 
 max  | tokyo   | 
(2 rows)

Если вы хотите использовать уникальные записи, игнорируя нулевые значения, вы должны создать условный уникальный индекс

postgres=# CREATE UNIQUE INDEX test_index ON test (name, address) WHERE college IS NULL;
CREATE INDEX
postgres=# INSERT INTO test (name, address) VALUES ('john', 'rome');
INSERT 0 1
postgres=# INSERT INTO test (name, address) VALUES ('max', 'tokyo');
INSERT 0 1
postgres=# INSERT INTO test (name, address, college) VALUES ('john', 'rome', 'college');
INSERT 0 1
postgres=# INSERT INTO test (name, address) VALUES ('john', 'rome');
ERROR:  duplicate key value violates unique constraint "test_index"
DETAIL:  Key (name, address)=(john, rome) already exists.

НТН

Ответ 3

Если вы сделаете его первичным ключом, вместо уникального ограничения, это сработает. Для этого столбец college должен быть NOT NULL и использовать (например) пустые строки вместо значений NULL. Или вы ищете запрос?

Ответ 4

NULL неизвестен, поэтому значение NULL, равное NULL, никогда не может быть истинным. Чтобы обойти этот закон, сделайте это.

Создайте новую справочную таблицу для ваших колледжей. В этой таблице есть запись со значением None. Затем поместите внешний ключ в новую таблицу поиска колледжа.

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

CREATE TABLE college(college_id SERIAL PRIMARY KEY,college_type);
INSERT INTO college(college_type)
SELECT 1,None;


create test (
name varchar(10),
address varchar(20),
college_id INTEGER NOT NULL DEFAULT 1,
constraint test_uq unique (name,address,college_id);