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