Re: Case insensitive selects?

Поиск
Список
Период
Сортировка
От David Wheeler
Тема Re: Case insensitive selects?
Дата
Msg-id Pine.LNX.4.21.0102171049460.30549-100000@theory
обсуждение исходный текст
Ответ на Re: Case insensitive selects?  (Martijn van Oosterhout <kleptog@svana.org>)
Список pgsql-general
On Sun, 18 Feb 2001, Martijn van Oosterhout wrote:

> Tom Lane wrote:
>
> [snip]
> >
> > Then why are you bothering to maintain a case-sensitive index?
> >
> > 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.
>
> I've actually been thinking about this and maybe this is possible with
> some smarts in the query parser. If you have an index on
> lower(fieldname) then consider the following query:
>
> select *
> from table1, table2
> where table1.a = table2.b;
>
> (the index is on lower(table1.a).
>
> Now, it should be true that a = b implies lower(a) = lower(b), so the
> above query is equivalent to:
>
> select *
> from table1, table2
> where table1.a = table2.b
> and lower(table1.a) = lower(table2.b);
>
> This query can use the index and produce the correct result. Am I
> missing anything?

This is almost exactly what I was thinking of.

David


В списке pgsql-general по дате отправления:

Предыдущее
От: Peter Eisentraut
Дата:
Сообщение: Re: Function reference
Следующее
От: Feite Brekeveld
Дата:
Сообщение: dump of dictionary