Re: Alternatives to a unique indexes with NULL

Peter Hicks <peter(dot)hicks(at)poggs(dot)co(dot)uk> wrote:

> All,
>
> I have a Rails application on 9.3 in which I want to enforce a unique
> index on a set of fields, one of which includes a NULL-able column.
>
> According to
> http://www.postgresql.org/docs/9.3/static/indexes-unique.html, btree
> indexes can't handle uniqueness on NULL columns, so I'm looking for
> another way to achieve what I need.

somethink like that? :

test=# create table peter_hicks (id int);
CREATE TABLE
Time: 1,129 ms
test=*# create unique index idx_1 on peter_hicks ((case when id is null
then 'NULL' else '' end)) where id is null;
CREATE INDEX
Time: 14,803 ms
test=*# insert into peter_hicks values (1);
INSERT 0 1
Time: 0,385 ms
test=*# insert into peter_hicks values (2);
INSERT 0 1
Time: 0,145 ms
test=*# insert into peter_hicks values (null);
INSERT 0 1
Time: 0,355 ms
test=*# insert into peter_hicks values (null);
ERROR: duplicate key value violates unique constraint "idx_1"
DETAIL: Key ((
CASE
WHEN id IS NULL THEN 'NULL'::text
ELSE ''::text
END))=(NULL) already exists.
Time: 0,376 ms
test=*#

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°