Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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..

share|improve this question
    
A different platform & version change, especially a major version is far from "identical" configurations as you seem to believe it is. To rule out the obvious, can you select all he rows (no WHERE clause) successfully, and have you checked the manual/list of changes on the Postgres site? If so what dies Postgres tell you when you EXPLAIN the query? –  voretaq7 Feb 14 '11 at 5:26
2  
ILIKE depends on the locale settings so you should compare these on the two machines. –  Milen A. Radev Feb 14 '11 at 8:30
    
Create a test case as simple as possible, a table with just a single record. Do it on both databases and find out what's going on. I guess you're using different content in the table on server B, the SQL looks fine. Altrough I wouldn't use the dollar-quoting approach, just normal quotes ' to stay close to ANSI-SQL. –  Frank Heikens Feb 14 '11 at 8:36
    
@Milen how can I compare them ? It seems that when the $$%кирилица%$$ is in a language different than English it doesn't return anything. I think you're suggestion is close to the answer. :) –  tftd Feb 14 '11 at 15:41
1  
Take a look at "22.1. Locale Support", and especially at "22.1.3. Problems". –  Milen A. Radev Feb 14 '11 at 16:07
show 2 more comments

1 Answer

up vote 1 down vote accepted

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".

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.