Re: Unique index problem

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