CODEWITHSUNDEEP
postgresqlpostgresql-9.3
Franz Thomsen
Franz Thomsen

Reputation: 484

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

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?

Upvotes: 4

Views: 10726

Answers (2)

Craig Ringer
Craig Ringer

Reputation: 324475

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.

Upvotes: 8

harmic
harmic

Reputation: 30587

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.

Upvotes: 5

Related Questions