22

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

Mani Deep
  • 1,298
  • 2
  • 17
  • 33
  • You cannot change the database collation. You need to re-create (dump/reload) the database with the new collation –  Oct 17 '13 at 07:58
  • when i try to create a database with en_IN unable to do it :( – Mani Deep Oct 17 '13 at 07:59
  • currently im using my own local system as a server.. and is already running. now i got a new ubuntu 12.04 amazon ec2 instance from amazon and im trying to move my server. so how do u say should i do that? plz – Mani Deep Oct 17 '13 at 08:05

4 Answers4

16

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
Chris
  • 185
  • 1
  • 2
  • I tried this.. but collation functions (e.g. lower()) weren't working correctly so I don't think this is a proper solution – remo May 12 '20 at 22:22
  • 6
    This worked for me on Postgres 12.4. I had to restart the database though. – Ilya Semenov Oct 20 '20 at 05:09
  • Don't! Do! This! In my case changing collate from c.utf-8 to utf8.uk_UA brake operation comparative with string. So select * from user where name = 'admin' - returns 0 rows. Positive: now I know how to append non-unique data to column with uniq :) – potapuff Oct 07 '21 at 06:04
15

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.

Yaroslav Nikitenko
  • 1,695
  • 2
  • 23
  • 31
13

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.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • You will certainly want to read the docs and see how your system locale is set up, but you can generally: `initdb --lc-collate en_IN --lc-ctype en_IN -D ....` – Chris Travers Nov 30 '13 at 12:48
-8

its Very very Simple Solutions.

Step1. su - postgres
Step2. psql
Setp3. update pg_database set encoding = pg_char_to_encoding('UTF8') where datname = 'icinga'; (dont'forget to add ;)
Step4. \l to check

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
zaman linux
  • 5