Thread: 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" 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. 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" 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. 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" 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.