Re: Postgres case insensitive searches
Lee Hachadoorian <lee.hachadoorian+l@gmail.com>
From: Lee Hachadoorian <Lee.Hachadoorian+L@gmail.com>
To: bhanu udaya <udayabhanu1984@hotmail.com>
Cc: Alban Hertroys <haramrae@gmail.com>,
Albe Laurenz <laurenz.albe@wien.gv.at>,
"pgsql-general@postgresql.org" <pgsql-general@postgresql.org>,
"pgadmin-support@postgresql.org" <pgadmin-support@postgresql.org>
Date: 2013-06-29T14:02:55Z
Lists: pgsql-general
Attachments
- (unnamed) (text/html)
<html>
<head>
<meta content="text/html; charset=ISO-8859-1"
http-equiv="Content-Type">
</head>
<body text="#000000" bgcolor="#FFFFFF">
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 we are 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: <a class="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 your results, just the search
condition:<br>
> <br>
> SELECT id, val FROM t WHERE upper(val) LIKE 'AB%' ORDER
BY val;<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 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.<br>
> <br>
> 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?<br>
> <br>
</div>
</div>
</blockquote>
<br>
Duplicate the 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. Use triggers to keep val and val_lower in sync and
discard all attempts to write directly to val_lower. Then all
queries would be of the form<br>
<br>
SELECT id, val<br>
FROM foo<br>
WHERE val_lower LIKE 'ab%';<br>
<br>
Wouldn't want to 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>
</body>
</html>