Weird "LIKE" behaviour
Below are two queries that should be pretty much the same but with the
first one, I'm trying to boil it down to a minimal test-case so I
don't have to export the table definition of dcm.providers. The first
one returns nothing but at least executes the query.
=> create table foo ( foo text );
=> select * from foo where foo like 'FOO%\'
The second one fails to execute the query...
=> select * from dcm.providers where lname like 'FOO%\'
ERROR: LIKE pattern must not end with escape character
Our server is 9.1.4 and can reproduce this behaviour with either 8.4
or 9.1 clients.
--
Andy Chambers
Software Engineer
(e) achambers@mcna.net
(t) 954-682-0573
CONFIDENTIALITY NOTICE: This electronic mail may contain information
that is privileged, confidential, and/or otherwise protected from
disclosure to anyone other than its intended recipient(s). Any
dissemination or use of this electronic mail or its contents by
persons other than the intended recipient(s) is strictly prohibited.
If you have received this communication in error, please notify the
sender immediately by reply e-mail so that we may correct our internal
records. Please then delete the original message. Thank you.
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Andy Chambers
Sent: Friday, July 06, 2012 4:17 PM
To: pgsql
Subject: [GENERAL] Weird "LIKE" behaviourBelow are two queries that should be pretty much the same but with the
first one, I'm trying to boil it down to a minimal test-case so I don't
have to
export the table definition of dcm.providers. The first one returns
nothing
but at least executes the query.
=> create table foo ( foo text );
=> select * from foo where foo like 'FOO%\'The second one fails to execute the query...
=> select * from dcm.providers where lname like 'FOO%\'
ERROR: LIKE pattern must not end with escape characterOur server is 9.1.4 and can reproduce this behaviour with either 8.4 or
9.1
clients.
The only part of the table "dcm.providers" that should matter is the data
type of the "lname" column - which you have not provided.
David J.
On Fri, Jul 6, 2012 at 4:26 PM, David Johnston <polobo@yahoo.com> wrote:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Andy Chambers
Sent: Friday, July 06, 2012 4:17 PM
To: pgsql
Subject: [GENERAL] Weird "LIKE" behaviourBelow are two queries that should be pretty much the same but with the
first one, I'm trying to boil it down to a minimal test-case so I don'thave to
export the table definition of dcm.providers. The first one returns
nothing
but at least executes the query.
=> create table foo ( foo text );
=> select * from foo where foo like 'FOO%\'The second one fails to execute the query...
=> select * from dcm.providers where lname like 'FOO%\'
ERROR: LIKE pattern must not end with escape characterOur server is 9.1.4 and can reproduce this behaviour with either 8.4 or
9.1
clients.
The only part of the table "dcm.providers" that should matter is the data
type of the "lname" column - which you have not provided.
I thought it might be due to the datatype too. Originally it was
character varying (30) but I "alter table'd" it to text to see if that
was the problem. Perhaps the fact that it was originally a varchar
makes the difference.
I'll check that now.
Thanks,
Andy
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Andy Chambers
Sent: Friday, July 06, 2012 4:34 PM
To: pgsql
Subject: Re: [GENERAL] Weird "LIKE" behaviourOn Fri, Jul 6, 2012 at 4:26 PM, David Johnston <polobo@yahoo.com> wrote:
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Andy Chambers
Sent: Friday, July 06, 2012 4:17 PM
To: pgsql
Subject: [GENERAL] Weird "LIKE" behaviourBelow are two queries that should be pretty much the same but with
the first one, I'm trying to boil it down to a minimal test-case so I
don'thave to
export the table definition of dcm.providers. The first one returns
nothing
but at least executes the query.
=> create table foo ( foo text );
=> select * from foo where foo like 'FOO%\'The second one fails to execute the query...
=> select * from dcm.providers where lname like 'FOO%\'
ERROR: LIKE pattern must not end with escape characterOur server is 9.1.4 and can reproduce this behaviour with either 8.4
or9.1
clients.
The only part of the table "dcm.providers" that should matter is the
data type of the "lname" column - which you have not provided.I thought it might be due to the datatype too. Originally it was
character
varying (30) but I "alter table'd" it to text to see if that was the
problem.
Perhaps the fact that it was originally a varchar makes the difference.
I'll check that now.
Actually, in all likelihood the issue is that your test case executed on an
empty table and so the WHERE clause has no need to be evaluated and thus the
runtime exception on the literal 'FOO%\' is never encountered. Had the
table contained data you would have seen the same error.
LIKE requires an escape character independent of the literal encoding since
it has defined special characters ("%" and "_") and the "\" escapes those so
that they may be used as-is when needed. You can use the optional ESCAPE
clause to get around this OR you can escape the escape character.
lname LIKE 'FOO%\\' OR lname LIKE 'FOO%\' ESCAPE '^' --you can use the
empty-string to disable escaping
If you were to use E'' syntax you'd have to do:
E'FOO%\\\\' -- 4 backslashes
http://www.postgresql.org/docs/9.1/interactive/functions-matching.html#FUNCT
IONS-POSIX-REGEXP
David J.