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
John McKown
