Re: String comparison problem in select - too many results

On Thu, Jan 11, 2018 at 9:57 AM, Durumdara <durumdara(at)gmail(dot)com> wrote:
> I tried in in different servers, different databases.
> 1.) Windows local PG: LC_COLLATE = 'Hungarian_Hungary.1250' - ok.
> 2.) Linux remote PG: LC_CTYPE = 'en_US.UTF-8' - also wrong!!! - UTF
> problem???

Your problem seems to be you consider wrong anything that doesn't
match your expectation.

> 3.) Forcing C collation: - ok
> 4.) Replace '/' to 'A': - ok

... More examples zapped, as they do not prove anything.

> The main problem that we have many searches in programme where we suppose
> good evaluation, and we have more sites with different servers (and
> collation).

You must define good evaluation. In your case it seems you consider
good evaluation is lexicographical comparison of ascii char values.
This is called 'C' collation and you have been told to it.

If your "programme" is doing string comparison in server collation and
you need good comparison, defined as C collation, that is a bug. Fix
it.

I would recommend reading
https://www.postgresql.org/docs/9.6/static/collation.html and related
docs, but someone who so boldly states postgres collations are
good/bad surely knows all about it.

Try to build from this:

with xx(x) as (values ('18/0113'),('18/0212'),('180/2010'))
select x collate "C" from xx order by 1;

Francisco Olarte.