From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Andreas Kretschmer <akretschmer(at)spamfence(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Alternatives to a unique indexes with NULL |
Date: | 2015-01-18 18:06:36 |
Message-ID: | 20150118180636.GC25809@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2015-01-18 19:04:16 | Re: Surrogate pairs in UTF-8 |
Previous Message | Martijn van Oosterhout | 2015-01-18 18:03:16 | Re: Surrogate pairs in UTF-8 |