1
+-----------+----------+------------+----------------------------+----------------------------+-----------------------+
| Name      | Owner    | Encoding   | Collate                    | Ctype                      | Access privileges     |
|-----------+----------+------------+----------------------------+----------------------------+-----------------------|
| postgres  | postgres | UTF8       | English_United States.1252 | English_United States.1252 | <null>                |
| template0 | postgres | UTF8       | English_United States.1252 | English_United States.1252 | =c/postgres           |
|           |          |            |                            |                            | postgres=CTc/postgres |
| template1 | postgres | UTF8       | English_United States.1252 | English_United States.1252 | =c/postgres           |
|           |          |            |                            |                            | postgres=CTc/postgres |
+-----------+----------+------------+----------------------------+----------------------------+-----------------------+

The value of Collate and Ctype is C.UTF-8 on Linux, but it is 1252 on Windows.

Can I change these values to C.UTF-8?

EnDelt64
  • 1,270
  • 3
  • 24
  • 31

1 Answers1

6

You cannot to change these values for already created databases. In this moment, when there are not other databases, the most easy solution is a) stop database, b) delete data directory, c) run manually initdb with options --encoding and --locale (run this command under postgres user). if you have some data there, backup (with pg_dump) first.

You can change these parameters when you create new database too (this is SQL command):

CREATE DATABASE test ENCODING='UTF8' LOCALE='C' TEMPLATE='template0';

Locale can be overwritten by clause COLLATE used in CREATE TABLE or SELECT commands.

Pavel Stehule
  • 42,331
  • 5
  • 91
  • 94