Re: Unique index problem

On Sun, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
> On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor(at)caido(dot)ro> wrote:
>> Hello
>>
>> I created a unique index that doesn't seem to work when one column is NULL.
>> Index is created like this: CREATE UNIQUE INDEX lab_tests_groups_siui_uni ON
>> lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups, valid_from,
>> id_lab_sample_types);
>> Now I can run this insert twice and I will have 2 records in the database
>> that seem to violate this index:
>> INSERT INTO lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>> valid_from) VALUES(463, 9183, '2014-06-01');
>>
>> When I create the index like this "CREATE UNIQUE INDEX
>> lab_tests_groups_siui_uni ON lab_tests_groups_siui(id_lab_tests_siui,
>> id_lab_tests_groups, valid_from);" index works fine.
>>
>> I tested this on postgres 9.1.4 and 9.1.9.
>
> This is normal operation, as one NULL is unique from other NULLS, as
> far as the db is concerned. If you want it to work some other way, you
> need to use a value other than null, or make an index that's something
> like un

dangit, stupid gmail sent early. anyway.

you'd have to make an index like unique index on (x,y,z) where field
is [not] null or something like that.

Basically NULL <> NULL <> a particular value.