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> |
Subject: | Re: Unique index problem |
Date: | 2015-12-20 21:44:35 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D88420C902E@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
____________________________________
pgsql-general-owner(at)postgresql(dot)org [pgsql-general-owner(at)postgresql(dot)org]" im Auftrag von "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
From | Date | Subject | |
---|---|---|---|
Next Message | Jim Nasby | 2015-12-20 23:17:45 | Re: Unique index problem |
Previous Message | Amitabh Kant | 2015-12-20 21:43:42 | Re: Moving Postgresql 9.1 instance from FreeBSD 9.0 machine to FreeBSD 10.2 |