setting a default collation for a postgresql database

Question

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 ;
 upper 
-------
 INCI
(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.

Answer 1

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;
CREATE DATABASE
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));
CREATE TABLE 
lctest=# insert into lc_test values ('inci');
INSERT 0 1
lctest=# select upper(one) from lc_test ;
 upper
-------
 İNCİ
(1 row) 

Hope this help MarcoP.

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