I have created a database with Turkish collation.

createdb -l tr_TR test_tr -E LATIN5 -T template0

Then a table with a row.

\c test_tr    
create table turkish(one text);
insert into turkish values('inci');

Everything is expected.

test_tr=# \l
                                  List of databases
    Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
 test_tr    | postgres | LATIN5   | tr_TR       | tr_TR       | 

The problem, I expect İNCİ but it gives me INCI. This is wrong for Turkish language.

test_tr=# select upper("one") from turkish ;
(1 row)

It turns out, the default collation is still English.

test_tr=# select upper("one" collate "tr_TR"),upper("one"), "one" from turkish ;
 upper | upper | one  
 İNCİ  | INCI  | inci

I know that this could be fixed via giving tr_TR collation while table creation like following:

create table turkish (one text collate "tr_TR"); 

So the question:

Is there any way to set default collation that Postgresql uses for table creation? That way, I can omit unnecessary collation specification on every table creation script.


you have to define it at database creation time, try this way:

postgres=# create database lctest encoding UTF8 LC_COLLATE 'tr_TR.utf8' LC_CTYPE 'tr_TR.utf8' template template0;
postgres=# \l
                                                  List of databases
                Name                 |     Owner      | Encoding |   Collate   |      Ctype    |   Access privileges  
 en_US.UTF-8 | lctest                | postgres       | UTF8     | tr_TR.utf8  | tr_TR.utf8  |  

postgres=# \c lctest
You are now connected to database "lctest" as user "postgres".
lctest=# create table lc_test (one varchar(10));
lctest=# insert into lc_test values ('inci');
lctest=# select upper(one)  from lc_test ;
(1 row)  

Hope this help MarcoP.

  • it looks like LATIN5 charset with tr_TR collation is not working properly. "createdb -l tr_TR.utf8 turkish -T template0" worked as expected. – Oğuzhan Topçu Dec 21 '18 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.