The following bug has been logged online:
Bug reference: 1282
Logged by: Justin Pasher
Email address: spam@pasher.org
PostgreSQL version: 7.4.2
Operating system: Debian Linux (unstable)
Description: LIKE clause double-unescapes characters
Details:
Perhaps I'm missing something, but I didn't see an explanation for this
behavior in the docs (I also hope this wasn't fixed in a newer version of
Postgres, as 7.4.2 is the only one I have access to). It looks like the
Postgres query parser "double-unescapes" values for the LIKE clause, but not
the = clause. Here's my example:
justinp=# CREATE TABLE "test" (id serial, first_name varchar(50), last_name
varchar(50), primary key(id));
NOTICE: CREATE TABLE will create implicit sequence "test_id_seq" for
"serial" column "test.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey"
for table "test"
CREATE TABLE
justinp=# INSERT INTO "test" ("first_name", "last_name") VALUES
('Test\\''n', 'Test''n');
INSERT 26586973 1
justinp=# SELECT * FROM "test";
id | first_name | last_name
----+------------+-----------
1 | Test\'n | Test'n
(1 row)
justinp=# SELECT * FROM "test" WHERE "first_name" = 'Test\\''n';
id | first_name | last_name
----+------------+-----------
1 | Test\'n | Test'n
(1 row)
justinp=# SELECT * FROM "test" WHERE "first_name" LIKE 'Test\\''n';
id | first_name | last_name
----+------------+-----------
(0 rows)
justinp=# SELECT * FROM "test" WHERE "last_name" LIKE 'Test\\''n';
id | first_name | last_name
----+------------+-----------
1 | Test\'n | Test'n
(1 row)
From the results, you can see that the same query with the = converted into
a LIKE causes the value of the right hand side to be decoded twice, (making
"Test\\''n" turn into "Test\'n", then turn into "Test'n"). It happens this
way whether you escape the single quote as '' or as \'.