Re: LIKE, CHAR(), and trailing spaces

On Wed, Feb 02, 2011 at 07:48:38PM -0500, Bruce Momjian wrote:
> Brendan Jurd wrote:
> > On 3 February 2011 10:54, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > > It seems LIKE is considering the trailing CHAR(10) field spaces as
> > > significant, even though our documentations says:
> > >
> > -- snip --
> > >
> > > It says trailing spaces are not significant for character comparisons
> > > --- the real question is whether LIKE is a comparison. ?Obvioiusly '='
> > > is a comparison, but the system does not treat LIKE as a comparison in
> > > terms of trailing spaces. ?Is that desired behavior?
> >
> > Interesting. I would have to say that from the user point of view,
> > LIKE is definitely a comparison, and if the rest of the operators on
> > bpchar ignore whitespace then LIKE ought to as well.
> >
> > Is the situation the same for regex matches (~ operators)?
>
> Yes, I think so:
>
> test=> SELECT 'a'::char(10) ~ 'a$';
> ?column?
> ----------
> f
> (1 row)
>
> test=> SELECT 'a'::char(10) ~ 'a *$';
> ?column?
> ----------
> t
> (1 row)
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com

In my mind LIKE/~ are pattern matching operators and not a simple
comparison operator. PostgreSQL is doing the right thing in restricting
the somewhat bizarre treatment of trailing spaces to the '=' comparison
function. I can only imagine what would be needed to allow exceptions
to the pattern matching syntax to allow you to actually work with and
match the trailing spaces otherwise.

+10 for leaving the behavior as is.

Regards,
Ken
>
> + It's impossible for everything to be true. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>