Re: Alternatives to a unique indexes with NULL

On Sat, Jan 17, 2015 at 02:03:34PM +0100, Andreas Kretschmer wrote:
> 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

Note: COALESCE is probably the better choice here.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
-- Arthur Schopenhauer