Re: Postgres case insensitive searches

bhanu udaya wrote:
>>> What is the best way of doing case insensitive searches in postgres using Like.
>>
>> Table "laurenz.t"
>> Column | Type | Modifiers
>> --------+---------+-----------
>> id | integer | not null
>> val | text | not null
>> Indexes:
>> "t_pkey" PRIMARY KEY, btree (id)
>>
>>
>> CREATE INDEX t_val_ci_ind ON t ((upper(val) text_pattern_ops);
>>
>> ANALYZE t;
>>
>> EXPLAIN SELECT id FROM t WHERE upper(val) LIKE 'AB%';
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------
>> Index Scan using t_val_ci_ind on t (cost=0.01..8.28 rows=1 width=4)
>> Index Cond: ((upper(val) ~>=~ 'AB'::text) AND (upper(val) ~<~ 'AC'::text))
>> Filter: (upper(val) ~~ 'AB%'::text)
>> (3 rows)

> Thanks. But, I do not want to convert into upper and show the result.
> Example, if I have records as below:
> id type
> 1. abcd
> 2. Abcdef
> 3. ABcdefg
> 4. aaadf
>
> The below query should report all the above

No, it shouldn't :^)

> select * from table where type like 'ab%'. It should get all above 3 records. Is there a way the
> database itself can be made case-insensitive with UTF8 characterset. I tried with character type &
> collation POSIX, but it did not really help.

My solution is fast and efficient, it will call upper() only once
per query. I don't see your problem. Different database systems
do things in different ways, but as long as you can do what you need
to do, that should be good enough.

Yours,
Laurenz Albe