Re: Creating an index-type for LIKE '%value%' - Mailing list pgsql-general

From Oleg Bartunov
Subject Re: Creating an index-type for LIKE '%value%'
Date
Msg-id Pine.GSO.4.62.0502082016170.2906@ra.sai.msu.su
Whole thread Raw
In response to Re: Creating an index-type for LIKE '%value%'  ("Larry Rosenman" <ler@lerctr.org>)
List pgsql-general
On Tue, 8 Feb 2005, Larry Rosenman wrote:

> Oleg Bartunov wrote:
>> On Tue, 8 Feb 2005, Larry Rosenman wrote:
>>
>>> Oleg Bartunov wrote:
>>>> On Tue, 8 Feb 2005, Larry Rosenman wrote:
>>>>
>>>>>
>>>>> It doesn't seem to like pieces with hyphens ('-') in the name, when
>>>>> I try To update blacklist set new_domain_lt=text2ltree(domain) I
>>>>> get a Syntax error (apparently for the hyphens).
>>>>>
>>>>
>>>> Try change definition of ISALNUM on ltree.h
>>>>
>>>> #define ISALNUM(x)      ( isalnum((unsigned char)(x)) || (x) == '_'
>>>> )
>>>>
>>>> this was already discussed
>>>> http://www.pgsql.ru/db/mw/msg.html?mid=2034299
>>>>
>>> Thanks!
>>>
>>> Now, how can I make it always case-insensitive?
>>>
>>
>> from http://www.sai.msu.su/~megera/postgres/gist/ltree/
>>
>>   It is possible to use several modifiers at the end of a label:
>>
>>
>>             @     Do case-insensitive label matching
>>             *     Do prefix matching for a label
>>             %     Don't account word separator '_' in label matching,
>>                   that is 'Russian%' would match 'Russian_nations',
>> but not 'Russian'
>>
>>
>>
>>>
>>>
>>>
>>
>>      Regards,
>>          Oleg
>> _____________________________________________________________
>> Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg
>> Astronomical Institute, Moscow University (Russia) Internet:
>> oleg@sai.msu.su, http://www.sai.msu.su/~megera/
>> phone: +007(095)939-16-83, +007(095)939-23-83
>
> Does that apply to each node, or the entire string?
>
> I'd like to not have to parse the lquery string and make each node following
> it with an @.


I'm a little bit tired :), if you want case insenstive for the whole node,
you could use built-in fuinction 'lower(text)' !

use text2ltree(lower(text))

>
> LER
>
>
>

     Regards,
         Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

pgsql-general by date:

Previous
From: "Larry Rosenman"
Date:
Subject: Re: Creating an index-type for LIKE '%value%'
Next
From: "Surabhi Ahuja "
Date:
Subject: