Re: Unique index problem - Mailing list pgsql-general

Thank you.

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


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

>
>____________________________________
>  pgsql-general-owner@postgresql.org
>[pgsql-general-owner@postgresql.org]" im Auftrag von "Scott
>Marlowe [scott.marlowe@gmail.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.marlowe@gmail.com>
>wrote:
>  Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@caido.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
>
>
>
>
>
>
>
>
>