1

I have a multi-column unique index in postgresql. Sometimes one of these column values will be NULL, and I'd like the unique index to treat NULL as just another value. So that these two rows are NOT allowed:

1,2,NULL
1,2,NULL

Doing simple experiments at psql, this is exactly the behavior I see.

But I have a stress test where I do 20 concurrent inserts using 20 threads. Here, Postgres allows the identical rows to be inserted.

Is there any reason for this to happen? Maybe there's another difference between my psql experiment and my test experiment.

John Bachir
  • 22,495
  • 29
  • 154
  • 227

1 Answers1

2

An unique index will consider nulls to be different so those values can be inserted:

create table t (a int, b int, c int, unique (a, b, c));
insert into t (a, b, c) values
(1, 2, null),
(1, 2, null);
INSERT 0 2

Show your experiment in which that is not allowed.

Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • 1
    gah, frak, it was my `ActiveRecord` validation that was stopping it. not sure how i missed this obvious thing in my test (especially since i thought i was explicitly testing them separately). okay thanks! i'll do the double index solution @dondimichaelstroma linked to – John Bachir Apr 14 '13 at 20:04