On Sun, Dec 20, 2015 at 9:00 AM, Scott Marlowe <scott.marlowe@xxxxxxxxx> wrote: > On Sun, Dec 20, 2015 at 8:50 AM, Sterpu Victor <victor@xxxxxxxx> wrote: >> Hello >> >> 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 dangit, stupid gmail sent early. anyway. you'd have to make an index like unique index on (x,y,z) where field is [not] null or something like that. Basically NULL <> NULL <> a particular value. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
