"select ... where field like lower('%text%')" fails

It was doing exactly what the query told it to do. I believe if you
rewrite your query like this, you will get the desired results.

select ... where lower(field) like '%sometext%'

Be sure to build an index on lower(field) otherwise you will resort to
sequence scans.

Sean Kelly wrote:

Hello,

I was trying to send the following bug report from the web page
but it kept timing out. I hope this is the only time it arrives on the
list...

I am trying to search a varchar(x) field with a query like:

select ... where field like lower('%someText%')

In one field, if the value someText is at the very start then the
search fails. In another field, if the value someText is at the very
start then the search succeeds.

Here are some statements (the ones returning 0 rows should be
returning something):

isp=> \d user_tbl
Table "user_tbl"
Attribute |    Type     |        Modifier
-----------+-------------+-------------------------
username  | varchar(10) | not null
company   | varchar(80) | not null
email     | varchar(80) |
password  | varchar(20) | not null
active    | boolean     | not null default 'TRUE'
created   | timestamp   | not null
Index: user_tbl_pkey

isp=> SELECT username,company,active,created
isp-> from user_tbl where username;
username | company | active | created
----------+---------------------+--------+------------------------
sean | Sean's Test Company | t | 2001-01-14 14:01:58+00
(1 row)

isp=> SELECT username,company,active,created
isp-> from user_tbl where username like lower('%SEaN%');
username | company | active | created
----------+---------------------+--------+------------------------
sean | Sean's Test Company | t | 2001-01-14 14:01:58+00
(1 row)

isp=> SELECT username,company,active,created
isp-> from user_tbl where company like lower('%SEaN%');
username | company | active | created
----------+---------+--------+---------
(0 rows)

isp=> SELECT username,company,active,created
isp-> from user_tbl where company like lower('SEaN%');
username | company | active | created
----------+---------+--------+---------
(0 rows)

isp=> SELECT username,company,active,created
isp-> from user_tbl where company like lower('%EaN%');
username | company | active | created
----------+---------------------+--------+------------------------
sean | Sean's Test Company | t | 2001-01-14 14:01:58+00
(1 row)

Any advice?

Thanks,

--
Sean

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

--
---------------------------------------------------
Randy Hall Great Bridge, LLC
Sr. Knowledge Engineer 253 Monticello Avenue
Red Hat Certified Engineer Norfolk, VA 23510