The following bug has been logged online:

Bug reference: 2729
Logged by: Michael van Rooyen
Email address: mvanr (AT) bigfoot (DOT) com
PostgreSQL version: 8.1.4
Operating system: Linux
Description: Backslash escaping not working as expected
Details:

The following query from psql:

select * from product where name like '%\\%';

Yields products whose names end with a %. I would have expected it to yeild
products whose names contained a backslash.

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

"Michael van Rooyen" <mvanr (AT) bigfoot (DOT) com> writes:
Postgres defaults to assuming \ as the LIKE escape character, that is,
what you typed is equivalent to

select * from product where name like '%\\%' escape '\\';

You can get the behavior you're expecting by not having any escape character:

select * from product where name like '%\\%' escape '';

This is as explained in TFM:
http://www.postgresql.org/docs/8.1/s...FUNCTIONS-LIKE
although I notice that SQL92 says that there is no escape character by
default. We can't change our historical documented behavior on the
point unless we were willing to provide a configuration variable to
adjust it, and I'm not sure it's worth that.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq