Re: 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.

Thanks,
Andy