PostgreSQL ILIKE doesn't return any data?

Question

I'm testing an sql code that's meant to be used for searching records. I have database A(my home copy) and database B (a server for a site, the database is an absolute clone of database A). For the purpose of this example let's say I have a table with 3 columns -

1. username
2. firstname
3. lastname

Imagine you have a couple of records in the table that contain the words "test","testing","tester" in all of the columns. Now you'd want to select all the records that contain the word "test" in all of the columns. The appropriate sql query would be:

SELECT * FROM users WHERE username ILIKE $$%test%$$ OR firstname ILIKE $$%test%$$ OR lastname ILIKE $$%test%$$;  

his code returns the correct data on database A but it doesn't return anything on database B. The copies are identical - I've dumped db A (my local machine) and imported it to db B(the server). Only I have access to that server so nobody could have messed up things. Plus, I tried dropping db B and recreating it with the same import just to be sure and again no results after the sql query.

The only difference between db A and db B is that db A is running on a 32bit machine and db B runs on 64 bit machine. The versions are different -db A runs 8.3.5 and db B runs 9.0.2, the configuration is the default which comes when you install PostgreSQL.

Can this be related to the 32/64 bit OS? Is it because of the different versions or it's a setting I need to enable ? I'm 99,999% sure it's the sql I'm executing is the right one so I have ruled out that one..

Answer 1

ILIKE depends on the locale settings so you should compare these on the two machines. Take a look at "22.1. Locale Support", and especially at "22.1.3. Problems".