sqlpostgresqlunicodewebfaction

Postgres function upper doesn't work for cyrillic (version 9.4)

This question is similar to Postgres doesn't search case-insensitive unicode regex

But it appears that upper function doesn't work as well? Also, it seems that encoding on server utf8

I use webfaction private postgres instance on CentOS 6 and don't know how to set locale on cluster creation.

Please, help, how to fix it.

Solution

  • Change collation, like:

    b=#  select upper('утф'  COLLATE "C"), upper('утф'  COLLATE "en_US");
 upper | upper
-------+-------
 утф   | УТФ
(1 row)

    Changing default like:

    b=# create table clt (a text COLLATE "C");
CREATE TABLE
b=# insert into clt select 'утф';
INSERT 0 1
b=# select upper(a) from clt;
 upper
-------
 утф
(1 row)

b=# alter table clt alter column a set data type text COLLATE "en_US";
ALTER TABLE
b=# select upper(a) from clt;
 upper
-------
 УТФ
(1 row)

    Alternatively you can dump your data and restore it to db with right locale. On restore tables will be built with right collation:

    b=# CREATE DATABASE not_c ENCODING 'UTF8'
   lc_ctype='en_US.utf-8'
   lc_collate='en_US.UTF-8' TEMPLATE=template0;
CREATE DATABASE
b=# \c not_c
You are now connected to database "not_c" as user "postgres".
not_c=# create table clt (a text);
CREATE TABLE
not_c=# insert into clt select 'утф';
INSERT 0 1
not_c=# select upper(a) from clt;
 upper
-------
 УТФ
(1 row)