Null value isn't unique

Question

I have rows like these on postgres:

 name | address | college 

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

I create a table like this:

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

How can I make null values become unique, so the output can be like this:

 name | address | college 

 john | rome    |
 max  | tokyo   |

Answer 1

Postgres documentation claims that this behaviour is compliant with the SQL standard:

In general, a unique constraint is violated when there are two or more rows in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard[.]

One possibility is to rethink your schema (to be honest, a uniqueness constraint on name+address+college doesn't make a whole lots of sense in your example).

Answer 2

If you just need unique records in the query result use 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)

If you want to enforce unique records ignoring null values you must create a conditional unique index

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