Re: String comparison problem in select - too many results

Поиск
Список
Период
Сортировка
От Francisco Olarte
Тема Re: String comparison problem in select - too many results
Дата
Msg-id CA+bJJbwFw1J3jKAU5FWAn0MJ72iW5jRF52RQyOzvTnSGUwPG6Q@mail.gmail.com
обсуждение исходный текст
Ответ на Re: String comparison problem in select - too many results  (Durumdara <durumdara@gmail.com>)
Ответы Re: String comparison problem in select - too many results  (Durumdara <durumdara@gmail.com>)
Список pgsql-general
On Thu, Jan 11, 2018 at 9:57 AM, Durumdara <durumdara@gmail.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.


В списке pgsql-general по дате отправления:

Предыдущее
От: pinker
Дата:
Сообщение: RE: How Many Partitions are Good Performing
Следующее
От: Andreas Joseph Krogh
Дата:
Сообщение: ORDER BY custom type