Shachar Shemesh <psql@shemesh.biz> writes:
> One problem detected during that stage, however, was that the program
> pretty much relies on the collation being case insensitive. I am now
> trying to gather the info regarding adding case preserving to
> Postgresql. I already suggested that we do that by changing the
> procedures, and the idea was turned down. For example, a column UNIQUE
> constraint must enforce that only one instance of a string be present,
> case insensitive.
Is there a reason you can't just docreate unique index on mytable (upper(mycolumn))
?
> Now, one idea that floated through my mind, and I have not yet looked
> into how difficult it would be to implement was to define a new system
> wide collation, called, for example, en_USCI.
I don't know anything about the difficulty of this either. I suspect
that it would be a bad idea to create a collation in which strcoll could
claim that nonidentical strings are equal --- you would likely find that
surprising things break. You could try it though and see what happens.
> Assuming that fails, how hard would it be to create a case insensitive
> PostgreSQL?
It would be a serious mistake to try to make a "case insensitive
PostgreSQL", if by that you mean hacking the system structure to enforce
case insensitivity globally. You could reasonably think about inventing
a case-insensitive textual datatype, however. (In fact, I'd not be
surprised if someone did that already.) All you'd really need is to
write a set of case-insensitive comparison operators and define a btree
opclass with them. Everything else you might want to do could be
handled by making the type binary-equivalent to text and piggybacking
on the existing functions for text.
regards, tom lane
