Alternatives to a unique indexes with NULL

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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�

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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