Re: Unique index problem

Thank you.

I used the syntax with 2 indexes, it works for me.
But why does NULL != NULL?

------ Original Message ------
From: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>
To: "Sterpu Victor" <victor(at)caido(dot)ro>
Cc: "PostgreSQL General" <pgsql-general(at)postgresql(dot)org>; "Andreas
Kretschmer" <akretschmer(at)spamfence(dot)net>; "Scott Marlowe"
<scott(dot)marlowe(at)gmail(dot)com>
Sent: 12/20/2015 11:44:35 PM
Subject: AW: [GENERAL] Unique index problem

>
>____________________________________
> pgsql-general-owner(at)postgresql(dot)org
>[pgsql-general-owner(at)postgresql(dot)org]&quot; im Auftrag von &quot;Scott
>Marlowe [scott(dot)marlowe(at)gmail(dot)com]
>ndet: Sonntag, 20. Dezember 2015 17:02
>Sterpu Victor
>PostgreSQL General
>eff: Re: [GENERAL] Unique index problem
>
>un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
>wrote:
> Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor(at)caido(dot)ro> wrote:
>ello
>
>>>> 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
>
>
>Hello,
>
>> 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);
>
>assuming that only id_lab_sample_types can be null, you could cover
>this with 2 partial indexes:
>
>CREATE UNIQUE INDEX lab_tests_groups_siui_uni_a ON
>lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>valid_from) WHERE (id_lab_sample_types IS NULL);
>and
>CREATE UNIQUE INDEX lab_tests_groups_siui_uni_b ON
>lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
>valid_from, id_lab_sample_types) WHERE (id_lab_sample_types IS NOT
>NULL);
>
>There is a serious caveat though: queries that don't contains a
>"id_lab_sample_types IS [NOT] NULL" condition will ignore the index.
>
>Maybe there is also a way using DISTINCT(id_lab_tests_siui,
>id_lab_tests_groups, valid_from, id_lab_sample_types) in the index
>definition, but I've never tried that and suspect the planner will also
>have trouble to include such an index in the plan.
>
>regards,
>
>Marc Mamin
>
>
>
>
>
>
>
>
>