CODEWITHSUNDEEP
postgresqlpostgresql-8.4
Mani Deep
Mani Deep

Reputation: 1356

Change Database Collation, Ctype in Postgresql

how do I change Collation, cType to - en_IN from en_US.UTF-8

                              List of databases
   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
                                                             : postgres=CTc/postgres

my current postgresversion is 8.4 ive installed it using

sudo apt-get install postgresql-8.4 postgresql-contrib-8.4

im doing this in my ubuntu amazon server ec2

Upvotes: 26

Views: 92018

Answers (3)

Chris
Chris

Reputation: 223

I had to change to POSIX.UTF-8. I managed that with the following commands:

su postgres
psql
\l
update pg_database set datcollate='POSIX.UTF-8', datctype='POSIX.UTF-8' where datname='databasename';
\l

Upvotes: 19

Yaroslav Nikitenko
Yaroslav Nikitenko

Reputation: 1853

It's not necessary to recreate the whole database cluster. You need however to recreate your database.

Run createdb with these options (man createdb):

   -E encoding, --encoding=encoding
       Specifies the character encoding scheme to be used in this
       database. The character sets supported by the PostgreSQL server
       are described in Section 22.3.1, “Supported Character Sets”, in
       the documentation.

   -l locale, --locale=locale
       Specifies the locale to be used in this database. This is
       equivalent to specifying both --lc-collate and --lc-ctype.

   --lc-collate=locale
       Specifies the LC_COLLATE setting to be used in this database.

   --lc-ctype=locale
       Specifies the LC_CTYPE setting to be used in this database.

It seems you really can't change the collation of an existing database:

=> ALTER DATABASE dbname SET "Collate" To Russian;
ERROR:  unrecognized configuration parameter "Collate"

Note that you can set collation for a table or a column, see a good tutorial on collations in PostgreSQL.

Upvotes: 16

Chris Travers
Chris Travers

Reputation: 26464

My recommendation:

  1. take a pg_dumpall

  2. re-initialize the db cluster, making sure the locale information is correct

  3. restore your dump.

I have found that sometimes it is possible that one may have to create a db with template template0 (-T template0 from bash or WITH TEMPLATE template0 from psql) to use a non-init-db locale.

Upvotes: 15

Related Questions