Search Postgresql Archives

Re: Postgres case insensitive searches

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux