Re: Case preserving - suggestions

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