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
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.
Thanks and Regards
Radha Krishna
> From: laurenz(dot)albe(at)wien(dot)gv(dot)at
> To: udayabhanu1984(at)hotmail(dot)com; pgsql-general(at)postgresql(dot)org
> Subject: RE: Postgres case insensitive searches
> Date: Fri, 28 Jun 2013 12:32:00 +0000
>
> 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)
>
> Yours,
> Laurenz Albe