Re: PG SQL and LIKE clause

pá 13. 9. 2019 v 8:49 odesílatel Matthias Apitz <guru(at)unixarea(dot)de> napsal:

> El día Friday, September 13, 2019 a las 07:33:10AM +0200, Pavel Stehule
> escribió:
>
> > > We got to know that in CHAR columns with trailing blanks a
> > >
> > > SELECT ... FROM ... WHERE name LIKE 'Ali'
> > >
> > > does not match in 'name' having 'Ali '.
> > >
> > > I glanced through our code with grep pipelines and found some hundred
> > > places which would be affected by this problem. I'm not interested in a
> > > religious discussion if or if not this behaviour of PG is correcter or
> > > better than in Sybase. It's just different to Sybase.
> > >
> > > Any hints to address this problem? Or is there any compile time option
> > > for the PG server to address this?
> > >
> >
> > There is not simple solution - you should to write own patch and used
> > patched postgres.
>
> Hello,
>
> As we compiled our own PG 11.4 and as we're alone on the server with
> our software plus PG, this could be an option to consider.
>
> Do you have an idea where to look for this in the PG source. Maybe it's
> just deleting the trailing blanks from the column content before applying
> the string compare with regular expressions.
>

I am not a expert on this area, and I don't know all dependency. Some code
is in src/backend/utils/adt/like.c

Pavel

> Thanks
>
> matthias
> --
> Matthias Apitz, ✉ guru(at)unixarea(dot)de, http://www.unixarea.de/
> +49-176-38902045
> Public GnuPG key: http://www.unixarea.de/key.pub
> May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!
>