The following bug has been logged online: Bug reference: 5737 Logged by: Radu Ilies Email address: ilies.r...@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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
