In PostgreSQL on Windows what effect does the locale have on a database itself

Question

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?

Answer 1

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.