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(at)timbira(dot)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?
>
>