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

Question

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.

something you don't tell: `a=# select upper('утф'); upper ------- УТФ (1 row)` — Vao Tsun, Dec 05 '16 at 22:46
postgres=# select version(); PostgreSQL 9.4.10 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 ( Red Hat 4.8.5-4), 64-bit (1 row) postgres=# select upper('утф'); upper ------- утф (1 row) — Paul R, Dec 05 '16 at 22:57
Output of locale command is LANG=en_US.UTF-8 LC_CTYPE="en_US.UTF-8" LC_NUMERIC=uk_UA.UTF-8 LC_TIME=uk_UA.UTF-8 LC_COLLATE="en_US.UTF-8" LC_MONETARY=uk_UA.UTF-8 LC_MESSAGES="en_US.UTF-8" LC_PAPER=uk_UA.UTF-8 LC_NAME=uk_UA.UTF-8 LC_ADDRESS=uk_UA.UTF-8 LC_TELEPHONE=uk_UA.UTF-8 LC_MEASUREMENT=uk_UA.UTF-8 LC_IDENTIFICATION=uk_UA.UTF-8 LC_ALL= — Paul R, Dec 06 '16 at 08:25
os env would not help here - you can create db in different locale. Though I doubt now it is locale. anyway what this shows? `select setting from pg_settings where name in ('server_encoding','lc_collate'); `?.. — Vao Tsun, Dec 06 '16 at 08:49

Vao Tsun · Accepted Answer · 2016-12-06T09:28:55.027

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)

How do I do it for all tables with one statement? – Paul R Dec 06 '16 at 09:21 — Paul R, Dec 06 '16 at 09:21

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

1 Answers1