Re: create custom collation from case insensitive portuguese

Thank you all for your help !

When mentioned in Portuguese case-insensitive in fact we are also 
talking about accent-insensitive

A common example is that the name Jose and José also can be written, 
citext or ilike only not solve the problem

My progress is ...

Edit file /usr/share/i18n/locales/i18n e alter section tolower /, an 
example:

(<U00C9>,<U00E9>) e alter for (<U00C9>,<U0065>)

LOWER reproduce: LOWER("ITAPAGÉ") => "itapage",

Example success:

SELECT * FROM endereco WHERE LOWER(logradouro) LIKE LOWER('itapage%')

this behavior is reproduced in citext (logradouro is column citext)

SELECT * FROM endereco WHERE logradouro = 'itapage'
or
SELECT * FROM endereco WHERE logradouro LIKE 'itapage%'

All examples return the desired value "ITAPAGÉ"
Issue:

SELECT * FROM endereco WHERE logradouro LIKE 'itapage%'  NOT USE INDEX  
I tried   CREATE INDEX cep_ik_logradouro ON cep USING btree (logradouro);   CREATE INDEX like_index ON cep(logradouro
varchar_pattern_ops);  CREATE INDEX cep_ci_index ON cep(lower(logradouro) varchar_pattern_ops);
 

I've had success with using index
SELECT * FROM endereco WHERE LOWER(logradouro) LIKE LOWER('itapage%') 
and CREATE INDEX cep_ci_index ON cep(lower(logradouro) varchar_pattern_ops)
But I want to solve using only citext


Tank's

Alexandre Riveira
Brazil




Robert Haas escreveu:
> On Tue, Nov 2, 2010 at 8:40 AM, Euler Taveira de Oliveira
> <euler@timbira.com> wrote:
>   
>> Alexandre Riveira escreveu:
>>     
>>> I've achieved some success in changing collate operating system (linux)
>>> to generate sort of way of Brazil Portuguese hopes by adding the
>>> following code in LC_COLLATE
>>>
>>>       
>> This was already discussed; search the archives [1] [2].
>>
>>     
>>> So far, I understood the mechanism of change collate and reproduce in
>>> postgresql, and I could not generate a case-insensitive search, I
>>> believe that would change within the LC_COLLATE variable, but could not
>>> go any further than that.
>>>
>>>       
>> PostgreSQL doesn't support case-insensitive searches specifying the collate
>> per column yet. Look at [3]. But you could use ILIKE or regular expression to
>> achieve  that.
>>     
>
> Is citext also useful for this?
>
>