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: haram...@gmail.com
> Date: Sat, 29 Jun 2013 09:37:51 +0200
> CC: laurenz.a...@wien.gv.at; pgsql-general@postgresql.org;
> pgadmin-supp...@postgresql.org
> To: udayabhanu1...@hotmail.com
>
> On Jun 29, 2013, at 3:59, bhanu udaya <udayabhanu1...@hotmail.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.a...@wien.gv.at
> > > To: udayabhanu1...@hotmail.com; pgsql-general@postgresql.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-supp...@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support
