postgresql - Why does my LIKE statement fail with '\\_' for matching? -

i have database entry has entries this:

id | name | code_set_id 

i have particular entry need find:

 674272310 | raphodo/qrc_resources.py |      782732 

in rails app (2.3.8), have statement evaluates this:

select * fyles code_set_id = 782732 , name 'raphodo/qrc\\_resources.py%'; 

from reading on escaping, above query correct. supposed correctly double escape underscore. query not find record in database. these queries will:

select * fyles code_set_id = 782732 , name 'raphodo/qrc\_resources.py%';  select * fyles code_set_id = 782732 , name 'raphodo/qrc_resources.py%'; 

am missing here? why first sql statement not finding correct entry?

a single backslash in rhs of like escapes following character:

9.7.1. like
[...]
match literal underscore or percent sign without matching other characters, respective character in pattern must preceded escape character. default escape character backslash different 1 can selected using escape clause. match escape character itself, write 2 escape characters.

so literal underscore in pattern:

\_ 

and single backslash followed "any character" pattern:

\\_ 

you want see this:

raphodo/qrc\_resources.py% 

postgresql used interpret c-stye backslash escapes in strings default no longer, have use e'...' use backslash escapes in string literals (unless you've changed configuration options). string constants c-style escapes section of manual covers simple version these two:

name e'raphodo/qrc\\_resources.py%' name 'raphodo/qrc\_resources.py%' 

do same thing of postgresql 9.1.

presumably rails 2.3.8 app (or whatever preparing patterns) assuming older version of postgresql 1 you're using. you'll need adjust things not double backslashes (or prefix pattern string literals es).