Re: Case insensitive selects?

On Thu, 15 Feb 2001, Tom Lane wrote:

> Then why are you bothering to maintain a case-sensitive index?

Because while some queries do a case-insensitive query, others do not, in
the sense that I do not everywhere convert the string to compare to lower
case.

> There's no free lunch available here; if you think there is, then you
> are misunderstanding what an index is. Either the index is in
> case-sensitive order, or it's not.

Well, I think I understand pretty well what an index is. But I don't get
that the earlier example was of a case-insensitive index, but of an index
where all the entries were forced into lower case (or upper case, as the
case may be [pun not intended]). 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');

But then I wouldn't call the index I created "case-insensitive."

But I would be happy to know if I'm missing something here.

Thanks,

David