Re: Unique index problem

Sterpu Victor <victor(at)caido(dot)ro> 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.
>
> Thank you.

NULL isn't a compareable value, because of that you can't compare NULL
with NULL and so you can insert more than one NULL into the table.

You can set the column as NOT NULL or you can do something like this:

-- that's your problem
test=# create table foo (a int, b int, c int);
CREATE TABLE
test=*# create unique index on foo(a,b,c);
CREATE INDEX
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# rollback;
ROLLBACK

-- that's a solution
test=# create table foo (a int, b int, c int);
CREATE TABLE
test=*# create unique index on foo(coalesce(a::text,'NULL'),
coalesce(b::text,'NULL'), coalesce(c::text,'NULL'));
CREATE INDEX
test=*# insert into foo (a,b) values (1,2);
INSERT 0 1
test=*# insert into foo (a,b) values (1,2);
ERROR: duplicate key value violates unique constraint
"foo_coalesce_coalesce1_coalesce2_idx"
DETAIL: Key ((COALESCE(a::text, 'NULL'::text)), (COALESCE(b::text,
'NULL'::text)), (COALESCE(c::text, 'NULL'::text)))=(1, 2, NULL) already
exists.
test=*#

Maybe there are better solutions, it's a quick hack ;-)

>
>
> DISCLAIMER:

That's a public mailing list ...

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°