2015-12-21 7:39 GMT+01:00 Sterpu Victor <vic...@caido.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.ma...@intershop.de> > To: "Sterpu Victor" <vic...@caido.ro> > Cc: "PostgreSQL General" <pgsql-general@postgresql.org>; "Andreas > Kretschmer" <akretsch...@spamfence.net>; "Scott Marlowe" < > scott.marl...@gmail.com> > Sent: 12/20/2015 11:44:35 PM > Subject: AW: [GENERAL] Unique index problem > > >> ____________________________________ >> pgsql-general-ow...@postgresql.org >> [pgsql-general-ow...@postgresql.org]" >> im Auftrag von "Scott Marlowe [scott.marl...@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.marl...@gmail.com> >> wrote: >> Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <vic...@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 >> >> >> >> >> >> >> >> >> >> > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
