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