I have a database that has been created on a database server that was installed with "Default Locale" selected where asked for "Select the locale to be used by the new database cluster". It should have been set to a specific locale. Can I just change that afterwards, or do I have to create the database from scratch?
Asked
Active
Viewed 8,141 times
2 Answers
7
It affects the text encoding ("code page") chosen for the DB, as well as the collation (sort order) used for text.
Changing either requires that you dump the database, drop it, re-create it and restore a dump.
When creating the database you can specify a specific ENCODING, LC_CTYPE, LC_COLLATE etc to override the DB-system-wide defaults. You must use TEMPLATE template0 if you want to change the encoding when creating a DB.
Craig Ringer
- 307,061
- 76
- 688
- 778
-
1That'll teach me to try to answer on a phone. By the time I've typed my answer in on this thing you've answered and the OP has accepted! – harmic Jul 23 '15 at 11:37
-
@harmic Yeah. I mostly use phone for comments and notifications for that reason. Get tired of jumping to and from the symbol keyboard - markdown isn't phone friendly. For answers I suggest posting early then fleshing out in an edit. – Craig Ringer Jul 23 '15 at 11:46
4
The locale used when creating the cluster sets the locale of the template databases. This in turn affects the locale of any further databases you create in the cluster since they are initialized by copying a template database (if you don't specify another locale when creating the database - see below).
The locale affects aspects such as the collation and the encoding. Encoding is the way in which characters are encoded into bytes in the database.
You can specify a collation or encoding when creating a database, but only if creating the database from template0. See CREATE DATABASE
You cannot change the encoding of an existing database. You would have to dump and reload the database to get a different encoding.
