Thread: Alternatives to a unique indexes with NULL

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.

My initial thought is to replace the null with a single space (it's a
character varying(1) column), which will require some changes to
application code, but result in a cleaner process than the application
enforcing the uniqueness constraint.

Is there a better or cleaner way to do what I want?


Peter

Hi John

On 17/01/15 12:39, John McKown wrote:
> ​I read the above. As I understand it, you can have a unique index on
> a column which is NULL-able. That will guarantee that all the non-NULL
> values are unique. What it will not guarantee is that there will be at
> most one NULL value in the indexed column. Are you saying that what
> you want is a column with a unique index where you cannot have two or
> more rows with NULL in the indexed column?
That's correct - the application reads in a very old-format of
fixed-length file and, if any field in there is just a single space,
replaces it with a NULL, since a single space implies a null. However,
only one of the records needs a constraint on one of these fields.
> ​If so, then you will need to have a value to indicate the equivalent
> of NULL. Personally, I use a zero length string "" instead of a single
> blank ' '. This is value since you say this column is a "character
> varying(1)". Which seems a bit strange to me, but I don't know your
> application.
OK, that makes sense and it was more-or-less along the lines of what I
expected.  I like the idea of a zero-length string versus a single
space, so I'll go implement that.

I believe the column type a Rails-ism, which creates 'string' fields
with a length constraint of 1 as 'character varying(1)'.  Probably not
ideal, but there's usually a trade-off somewhere.

Thanks very much for your help and quick response!


Peter

Peter Hicks <peter.hicks@poggs.co.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°

On Sat, Jan 17, 2015 at 02:03:34PM +0100, Andreas Kretschmer wrote:
> Peter Hicks <peter.hicks@poggs.co.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@svana.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

On Sat, Jan 17, 2015 at 02:03:34PM +0100, Andreas Kretschmer wrote:
> Peter Hicks <peter.hicks@poggs.co.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@svana.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