Hello,
I agree that it is just search condition. But, in a 2.5 million record table search, upper function is not that fast. The expectation is to get the query retrieved in 100 ms...with all indexes used.
I tried with upper, Citext, but the result set was more than a second.
The OS server we are using is Linux 64 bit.
Thanks and Regards
Radha Krishna
> Subject: Re: [pgadmin-support] [GENERAL] Postgres case insensitive searches
> From: haramrae(at)gmail(dot)com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz(dot)albe(at)wien(dot)gv(dot)at; pgsql-general(at)postgresql(dot)org; pgadmin-support(at)postgresql(dot)org
> To: udayabhanu1984(at)hotmail(dot)com
>
> On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1984(at)hotmail(dot)com> wrote:
>
> > Thanks. But, I do not want to convert into upper and show the result.
>
> Why not? It won't modify your results, just the search condition:
>
> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY val;
>
> Or:
>
> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY upper(val), val;
>
>
> > 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.
>
> I was under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS collation mechanics). So, what OS are you running the server on?
>
> > > 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
>
> Please do not top-post on this list.
>
> Alban Hertroys
> --
> If you can't see the forest for the trees,
> cut the trees and you'll find there is no forest.
>
>
>
> --
> Sent via pgadmin-support mailing list (pgadmin-support(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support