<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html;charset=ISO-8859-1">
<title></title>
</head>
<body>
Rajesh Kumar Mallah wrote:<br>
<blockquote type="cite" cite="mid3CBFFB30(dot)C9F29632(at)trade-india(dot)com"> Hi
folks,
<p>can any one help me with this. </p>
<p><tt><font color="#000099">tradein_clients=> explain SELECT email_id
from email_bank where lower(email) = '<a class="moz-txt-link-abbreviated" href="mailto:mallah(at)grex(dot)org">mallah(at)grex(dot)org</a>' ;</font></tt> <br>
<tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt> </p>
<p><tt><font color="#000099">Seq Scan on email_bank (cost=0.00..25223.02
rows=9385 width=4)</font></tt> </p>
<p><tt><font color="#000099">EXPLAIN</font></tt> <br>
<tt><font color="#000099">tradein_clients=> explain </font></tt><tt><font
color="#000099">SELECT email_id from email_bank </font></tt><tt><font
color="#000099">where email = '<a class="moz-txt-link-abbreviated" href="mailto:mallah(at)grex(dot)org">mallah(at)grex(dot)org</a>' ;</font></tt> <br>
<tt><font color="#000099">NOTICE: QUERY PLAN:</font></tt> </p>
<p><tt><font color="#000099">Index Scan using email_bank_email_key on email_bank
(cost=0.00..4.83 rows=1 width=4)</font></tt> </p>
<p><tt><font color="#000099">EXPLAIN</font></tt> </p>
<p>i want to query the data in case insensetive manner , but if i put lower(email)
<br>
index is not being used any workarounds?? <br>
</p>
<p>regds <br>
Mallah.<br>
</p>
</blockquote>
ILIKE is the case insensitive comparison operator. <br>
<br>
SELECT email_id from email_bank WHERE email ILIKE '<a class="moz-txt-link-abbreviated" href="mailto:user(at)domain(dot)org">user(at)domain(dot)org</a>'<br>
<br>
You might also want to create an using the lower function.<br>
<br>
CREATE INDEX my_foo_index ON LOWER(email_bank(email));<br>
</body>
</html>