Peter Hicks <peter.hicks@xxxxxxxxxxx> 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° -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
