Re: Unique index problem

Seems to be working also with a single index, like this:
CREATE UNIQUE INDEX lab_tests_groups_siui_uni_c ON
lab_tests_groups_siui(id_lab_tests_siui, id_lab_tests_groups,
valid_from, coalesce(id_lab_sample_types, 0));

------ Original Message ------
From: "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com>
To: "Sterpu Victor" <victor(at)caido(dot)ro>
Cc: "Marc Mamin" <M(dot)Mamin(at)intershop(dot)de>; "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/21/2015 8:44:14 AM
Subject: Re: [GENERAL] 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
>