Re: Case insensitive selects?

David Wheeler <david(at)wheeler(dot)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