空值不是唯一的

Question

我在 postgres 上有这样的行：

 name | address | college 

 john | rome    |
 john | rome    |
 max  | tokyo   |

我创建了一个这样的表：

create test (
name    varchar(10),
address varchar(20),
college varchar(20),
constraint test_uq unique (name,address,college);

如何使空值变得唯一，所以输出可以是这样的：

 name | address | college 

 john | rome    |
 max  | tokyo   |

Answer 1

Postgres 文档声明此行为符合 SQL 标准：

一般来说，当有两个或多个时，就会违反唯一约束
表中的行，其中包含的所有列的值
约束条件相等。但是，不考虑两个空值
在这个比较中相等。这意味着即使存在独特的
约束可以存储包含空值的重复行
至少一个受约束列中的值。这种行为
符合SQL标准[.]

一种可能性是重新考虑您的架构（说实话，对 name+address+college 的唯一性约束在您的示例中没有多大意义）。

Answer 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.

HTH

Answer 3

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

Answer 4

如果将其设为主键，而不是唯一约束，它就会起作用。为此，college 列必须为 NOT NULL 并使用（例如）空字符串而不是 NULL 值。
或者您正在寻找查询？