Re: Unique index problem

2015-12-21 7:39 GMT+01:00 Sterpu Victor <victor(at)caido(dot)ro>:

> Thank you.
>
> I used the syntax with 2 indexes, it works for me.
> But why does NULL != NULL?
>
>
because it was designed

http://www.w3schools.com/sql/sql_null_values.asp

Pavel

>
> ------ 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>