Backslashes doesn't work in LIKE queries with PostgreSQL adapter

I have realised that LIKE clauses require special escaping of %, _, and . PostgreSQL defaults to having backslash as the escape operator, while SQLite default to NOT having an escape operator. Furthermore, PostgreSQL requires escaping of backslashes in strings in general, while SQLite does not.

To select strings containing "%_" would in SQLite be:

select ... where name like '\%_\' ESCAPE ''

and in PostgreSQL

select ... where name like E'\%\_\\' ESCAPE '\';

A portable way seems to be:

filter.gsub!(/[\%_]/, "\\\0") Record.find(:all, :conditions => [ "name like ? escape ?", "%#{filter}%", "\" ]

-1 tested with master on postgresql 8.3.5 with ruby1.8.6 and unable to reproduce

>> u = User.create(:name => 'tra/la/la')
=> #<User id: 3, name: "tra/la/la", created_at: "2009-08-09 22:19:01", updated_at: "2009-08-09 22:19:01">
>> User.find(:all,:conditions => ["users.name like ?","%tra/la%"])
=> [#<User id: 3, name: "tra/la/la", created_at: "2009-08-09 22:19:01", updated_at: "2009-08-09 22:19:01">]