Alternatives to a unique indexes with NULL

Peter Hicks <peter.hicks@poggs.co.uk>

From: Peter Hicks <peter.hicks@poggs.co.uk>
To: pgsql-general@postgresql.org
Date: 2015-01-17T12:27:49Z
Lists: pgsql-general
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