Re: Case preserving - suggestions

Shachar Shemesh <psql(at)shemesh(dot)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 do
create 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