The following bug has been logged online:
Bug reference: 5737
Logged by: Radu Ilies
Email address: ilies.radu@gmail.com
PostgreSQL version: 8.3.11
Operating system: NetBSD
Description: LIKE and ILIKE strange behaviour
Details:
Hello,
I have a table with:
id, char[25]
name, text
The first row is:
id='radu'
name='Radu Ilies'
The following queries does not find any result (fail):
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'
But the following find one row (successful):
SELECT * FROM my_table WHERE id = 'radu'
--------- I have tried to change the column ID:
from CHAR[25] to CHAR VARYING[25]
and !!! Surprise, it works !!!
after changing, these queries are successful (find one row):
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'
SELECT * FROM my_table WHERE id = 'radu'
They are also successful if I change ID field as TEXT.
Please notice that I am new to PostGreSQL, I came from a 10 years MySQL
experience, but in my opinion THIS IS A BUG since even if there is a
difference in storage from CHAR[25] to CHAR VARYING[25] to TEXT,
if this is successful (returns one row)
SELECT * FROM my_table WHERE id = 'radu'
also the followings should be successful:
SELECT * FROM my_table WHERE id LIKE 'radu'
SELECT * FROM my_table WHERE id ILIKE 'radu'
but they are not in the case of CHAR[25]
if I use on CHAR[25] as
SELECT * FROM my_table WHERE id LIKE 'radu%'
SELECT * FROM my_table WHERE id ILIKE 'radu%'
they come successful. But they should be without trailing % as the value of
the field is 'radu' and the query is successful (returns one row)
SELECT * FROM my_table WHERE id = 'radu'
Cheers,
Radu