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

If you make it a primary key, instead of a unique constraint, it would work. For that, the column college would have to be NOT NULL and use (for instance) empty strings instead of NULL values. Or are you looking for a query?

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

Answer 3

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 4

NULL is unknown so a value of NULL being equal to NULL can never be true. To work around this law do this.

Create a new look-up table for your colleges. In that table have a record with the value None. Then put a Foreign Key to the new college look-up table.

This is pseudo code so you may have to mess with it to make it work, but here is the basic idea.

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