> -----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" behaviour
>
> 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" 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.
> >>
> >
> > 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.