____________________________________ pgsql-general-owner@xxxxxxxxxxxxxx [pgsql-general-owner@xxxxxxxxxxxxxx]" im Auftrag von "Scott Marlowe [scott.marlowe@xxxxxxxxx] ndet: Sonntag, 20. Dezember 2015 17:02 Sterpu Victor PostgreSQL General eff: Re: Unique index problem un, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@xxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
