Horton

Thread

  1. Alternatives to a unique indexes with NULL

    Peter Hicks <peter.hicks@poggs.co.uk> — 2015-01-17T12:27:49Z

    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

  2. Re: Alternatives to a unique indexes with NULL

    John McKown <john.archie.mckown@gmail.com> — 2015-01-17T12:39:16Z

    On Sat, Jan 17, 2015 at 6:27 AM, 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.
>
> 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?


​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? ​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.



>
>
>
> Peter
>

-- 
​
While a transcendent vocabulary is laudable, one must be eternally careful
so that the calculated objective of communication does not become ensconced
in obscurity.  In other words, eschew obfuscation.

111,111,111 x 111,111,111 = 12,345,678,987,654,321

Maranatha! <><
John McKown

  3. Re: Alternatives to a unique indexes with NULL

    Peter Hicks <peter.hicks@poggs.co.uk> — 2015-01-17T13:00:02Z

    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

  4. Re: Alternatives to a unique indexes with NULL

    Andreas Kretschmer <akretschmer@spamfence.net> — 2015-01-17T13:03:34Z

    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°

  5. Re: Alternatives to a unique indexes with NULL

    Martijn van Oosterhout <kleptog@svana.org> — 2015-01-18T18:06:36Z

    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