Re: Case insensitive selects?

David Wheeler <david@wheeler.net> writes:
> Thus, if I have this index:
>
> CREATE INDEX idx_mime_type__name ON mime_type(LOWER(name));
>
> and I execute this query:
>
> SELECT *
> FROM   mime_type
> WHERE  name = 'text/HTML';
>
> Will it use the index I created above or not? I'm assuming not unless I
> rewrite the query like this:
>
> SELECT *
> FROM   mime_type
> WHERE  name = LOWER('text/HTML');

Not then either; you'd need to write

SELECT *
FROM   mime_type
WHERE  LOWER(name) = LOWER('text/HTML');

or equivalently

SELECT *
FROM   mime_type
WHERE  LOWER(name) = 'text/html';

which is what will result from constant-folding anyway.

The details of invocation seem beside the point, however.  The point is
that a btree index is all about sort order, and the sort order of data
viewed case-sensitively is quite different from the sort order of
monocased data.  Perhaps in an ASCII universe you could play some tricks
to make the same index serve both purposes, but it'll never work in
non-ASCII locales ...

            regards, tom lane