Change Database Collation, Ctype in Postgresql

Question

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

You cannot change the database collation. You need to re-create (dump/reload) the database with the new collation — user330315
– user330315, Commented Oct 17, 2013 at 7:58
when i try to create a database with en_IN unable to do it :( — Mani Deep
– Mani Deep, Commented Oct 17, 2013 at 7: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
– Mani Deep, Commented Oct 17, 2013 at 8:05

Chris · Accepted Answer · 2020-05-04 15:02:06Z

21

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

answered May 4, 2020 at 15:02

Chris

2432 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

remo Over a year ago

I tried this.. but collation functions (e.g. lower()) weren't working correctly so I don't think this is a proper solution

Ilya Semenov Over a year ago

This worked for me on Postgres 12.4. I had to restart the database though.

potapuff Over a year ago

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 :)

bcag2 Over a year ago

works fine to switch from default en_US.utf8 to fr_FR.UTF-8 : update pg_database set datcollate='fr_FR.UTF-8', datctype='fr_FR.UTF-8' where datname = 'my_data_base_name';

Yaroslav Nikitenko · Accepted Answer · 2016-05-14 10:07:23Z

16

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.

answered May 14, 2016 at 10:07

Yaroslav Nikitenko

1,8732 gold badges25 silver badges32 bronze badges

2 Comments

minusf Over a year ago

might have to use -T template0 if initdb was run with a different encoding.

Yaroslav Nikitenko Over a year ago

@minusf yes, if the template has the encoding you need :)

Chris Travers · Accepted Answer · 2013-11-29 06:39:26Z

15

My recommendation:

take a pg_dumpall
re-initialize the db cluster, making sure the locale information is correct
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.

answered Nov 29, 2013 at 6:39

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

1 Comment

Chris Travers Over a year ago

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 ....

Collectives™ on Stack Overflow

Change Database Collation, Ctype in Postgresql

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related