From: | David Wheeler <david(at)wheeler(dot)net> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Case insensitive selects? |
Date: | 2001-02-15 17:10:32 |
Message-ID: | Pine.LNX.4.21.0102150904060.16929-100000@theory |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fork | 2001-02-15 17:15:04 | Re: Case insensitive selects? |
Previous Message | Tom Lane | 2001-02-15 16:58:17 | Re: Case insensitive selects? |