PostgreSQL – Setting a Default Collation for a Database

collationpostgresql

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.

Best Answer

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.

Related Question