Re: Postgres case insensitive searches - Mailing list pgsql-general

On 06/29/2013 09:02 AM, bhanu udaya wrote:<br /><blockquote cite="mid:COL127-W1529DEA2A427BDB5A28C9FD3770@phx.gbl"
type="cite"><style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 12pt;
font-family:Calibri
}
--></style><div dir="ltr">Hello,<br /> 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.<br/>  <br /> I tried with upper, Citext, but the result set was more than a second.<br />  <br /> The OS server
weare using is Linux 64 bit.<br />  <br /> Thanks and Regards<br /> Radha Krishna<br />  <br /><div>> Subject: Re:
[pgadmin-support][GENERAL] Postgres case insensitive searches<br /> > From: <a class="moz-txt-link-abbreviated"
href="mailto:haramrae@gmail.com">haramrae@gmail.com</a><br/> > Date: Sat, 29 Jun 2013 09:37:51 +0200<br /> > CC:
<aclass="moz-txt-link-abbreviated" href="mailto:laurenz.albe@wien.gv.at">laurenz.albe@wien.gv.at</a>; <a
class="moz-txt-link-abbreviated"href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>; <a
class="moz-txt-link-abbreviated"href="mailto:pgadmin-support@postgresql.org">pgadmin-support@postgresql.org</a><br />
>To: <a class="moz-txt-link-abbreviated" href="mailto:udayabhanu1984@hotmail.com">udayabhanu1984@hotmail.com</a><br
/>> <br /> > On Jun 29, 2013, at 3:59, bhanu udaya <a class="moz-txt-link-rfc2396E"
href="mailto:udayabhanu1984@hotmail.com"><udayabhanu1984@hotmail.com></a>wrote:<br /> > <br /> > >
Thanks.But, I do not want to convert into upper and show the result. <br /> > <br /> > Why not? It won't modify
yourresults, just the search condition:<br /> > <br /> > SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BYval;<br /> > <br /> > Or:<br /> > <br /> > SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER BY
upper(val),val;<br /> > <br /> > <br /> > > Example, if I have records as below:<br /> > > id type<br
/>> > 1. abcd<br /> > > 2. Abcdef<br /> > > 3. ABcdefg<br /> > > 4. aaadf<br /> > > <br
/>> > The below query should report all the above <br /> > > <br /> > > select * from table where
typelike 'ab%'. It should get all above 3 records. Is there a way the database itself can be made case-insensitive with
UTF8characterset. I tried with character type & collation POSIX, but it did not really help.<br /> > <br /> >
Iwas under the impression this would work, but ISTR that not every OS has this capability (Postgres makes use of the OS
collationmechanics). So, what OS are you running the server on?<br /> > <br /></div></div></blockquote><br />
Duplicatethe column with an upper or lowercase version and run all queries against that.<br /><br /> CREATE TABLE foo
(<br/>     id serial PRIMARY KEY,<br />     val text,<br />     val_lower text<br /> );<br /><br /> Index val_lower.
Usetriggers to keep val and val_lower in sync and discard all attempts to write directly to val_lower. Then all queries
wouldbe of the form<br /><br /> SELECT id, val<br /> FROM foo<br /> WHERE val_lower LIKE 'ab%';<br /><br /> Wouldn't
wantto write every table like this, but if (a) query speed trumps all other requirements and (b) functional index,
CITEXT,etc. have all been rejected as not fast enough…<br /><br /> --Lee<br /><br /><br /><pre class="moz-signature"
cols="72">--
 
Lee Hachadoorian
Assistant Professor in Geography, Dartmouth College
<a class="moz-txt-link-freetext" href="http://freecity.commons.gc.cuny.edu">http://freecity.commons.gc.cuny.edu</a>
</pre>