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   |

Related question about MySQL and non-unique nulls: http://stackoverflow.com/questions/4081783/unique-key-with-nulls — Sergey Orshanskiy, Oct 13 '13 at 03:17

score 2 · Accepted Answer · answered Oct 13 '11 at 10:54

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

oh my..so null value isnt be unique? so how to avoid redudancy data ? in my real table, i insert the record into table which in that table the unique constraint is true, then i insert again the same record, and that same record inserted to table, finally in that table there's 2 same record :( — Diaz Pradiananto, Oct 14 '11 at 02:38

score 2 · Answer 2 · answered Oct 14 '11 at 19:19

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

score 0 · Answer 3 · edited Oct 14 '11 at 20:52

0

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?

edited Oct 14 '11 at 20:52

Erwin Brandstetter

605,456
145
1,078
1,228

answered Oct 13 '11 at 10:42

Jan S

1,831
15
21

i cant make it primary key, because in my real data, a few record has a value, – Diaz Pradiananto Oct 14 '11 at 02:31
That is ok - even if some records have a value. You want that 2 records should not have the same (name,address,college) values, right? – Jan S Oct 14 '11 at 04:33
So then if you make it a primary key it would not allow duplicate entries where all 3 columns are the same. Why don't you try it with a sample table and see if it works for you? – Jan S Oct 14 '11 at 07:11
1

A primary key in postgres is simply [a combination of a unique constraint and a not-null constraint](http://www.postgresql.org/docs/current/interactive/ddl-constraints.html). No need for a primary key here. Just set the column `NOT NULL DEFAULT ''`, and you are done here. – Erwin Brandstetter Oct 14 '11 at 20:48
@ErwinBrandstetter Thank you :) I was thinking mysql, missed the postgres. – Jan S Oct 16 '11 at 16:28
@ErwinBrandstetter yapp thx for your help. it works when i insert a new value to that table with the same data. – Diaz Pradiananto Oct 18 '11 at 03:53

score 0 · Answer 4 · answered Oct 13 '11 at 16:29

0

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

answered Oct 13 '11 at 16:29

Kuberchaun

29,160
7
51
59

hmm i dont wanna make it become 2 table, because in my real data, i have to make it 1 table, because the purpose of my data is make a summary_report. but thanks for your answer. i aprociate it . thank you very much – Diaz Pradiananto Oct 14 '11 at 02:47
You can still do what I was saying but leave out the new table and instead replace NULL in your existing college table with NO COLLEGE or NA. – Kuberchaun Oct 14 '11 at 06:55
so i give the default value for college, then give the college constraint not null? – Diaz Pradiananto Oct 14 '11 at 07:05
Right, I believe that should get you out of your current issue. – Kuberchaun Oct 14 '11 at 07:09

Null value isn't unique

4 Answers4

Linked