Re: lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux - Mailing list pgsql-general

Hello

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Charles
> Clavadetscher
> Sent: Montag, 8. August 2016 09:30
> To: 'Alexander Farber' <alexander.farber@gmail.com>; 'pgsql-general' <pgsql-general@postgresql.org>
> Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows, but works on Mac, Linux
>
> Hello
>
> > From: pgsql-general-owner@postgresql.org
> > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Alexander
> > Farber
> > Sent: Montag, 8. August 2016 09:10
> > To: pgsql-general <pgsql-general@postgresql.org>
> > Subject: Re: [GENERAL] lower() silently fails for 9.5.3 on Windows,
> > but works on Mac, Linux
> >
> > More info:
> >
> > # \l+
> >
> >   Name    |  Owner  | Encoding | Collate | Ctype |
> > -----------+---------+----------+---------+-------+
> > postgres  | user1   | UTF8     | C       | C     |
> > template0 | user1   | UTF8     | C       | C     |
> >           |         |          |         |       |
> > template1 | user1   | UTF8     | C       | C     |
> >           |         |          |         |       |
> > words     | user1   | UTF8     | C       | C     |
>
>
> I cannot test on 9.5, which you are using, but I was able to reproduce the problem on 9.3:
>
> kofadmin@kofdb.localhost=> CREATE DATABASE test TEMPLATE template0 ENCODING 'UTF8' LC_COLLATE 'C' LC_CTYPE 'C';
> CREATE DATABASE kofadmin@kofdb.localhost=> \l
>                                                  List of databases
>    Name    |  Owner   | Encoding |          Collate           |           Ctype            |   Access privileges
> -----------+----------+----------+----------------------------+----------------------------+-----------------------
>  kofdb     | kofadmin | UTF8     | English_United States.1252 | English_United States.1252 |
>  test      | kofadmin | UTF8     | C                          | C                          |
>
> kofadmin@kofdb.localhost=> \c test
> psql (9.6devel, server 9.3.6)
> You are now connected to database "test" as user "kofadmin".
> kofadmin@test.localhost=> select lower('P');  <-- This works (latin letters)  lower
> -------
>  p
> (1 row)
>
> kofadmin@test.localhost=> select lower('И'); <-- This does not work (cyrillic)  lower
> -------
>  И
> (1 row)
>
> kofadmin@test.localhost=> \c kofdb
> psql (9.6devel, server 9.3.6)
> You are now connected to database "kofdb" as user "kofadmin".
> kofadmin@kofdb.localhost=> select lower('И'); <-- This works on a DB with another collation and ctype  lower
> -------
>  и
> (1 row)
>
> It seems to be a problem with collation and or ctype.
>
> What are the settings of the database on your Linux system where all works correct?

A possible workaround:

kofadmin@test.localhost=> CREATE COLLATION "en_US" (LOCALE = 'English_United States.1252');
CREATE COLLATION

kofadmin@test.localhost=> select * from pg_collation;
 collname | collnamespace | collowner | collencoding |        collcollate         |         collctype
----------+---------------+-----------+--------------+----------------------------+----------------------------
 default  |            11 |        10 |           -1 |                            |
 C        |            11 |        10 |           -1 | C                          | C
 POSIX    |            11 |        10 |           -1 | POSIX                      | POSIX
 en_US    |          2200 |     16394 |            6 | English_United States.1252 | English_United States.1252
(4 rows)

kofadmin@test.localhost=> with x as (select 'И'::text collate "en_US" as letter) select lower(letter) from x ;
 lower
-------
 и
(1 row)

Sure, bit uncomfortable.
Bye
Charles

>
> Bye
> Charles
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general