Postgres databases with different encoding

Question

I tried to create databases with different encoding in postgres (I'm using PgAdmin as my database manager), but when I try to create a database with non UTF8 encoding, PgAdmin shows me an error saying that I can't create the database because it does not match my current locale "Portuguese_Brazil_1252" and that the LC_Ctype configuration requires encoding "WIN1252".

How do I configure that in PostGres? I'm running it under a locale test server with windows XP (not my choice). Is it possible to create databases with different encoding or that behavior is a PostGres limitation? Anyone here already had success managing that?

Thanks

Answer 1

From the docs:

The character set encoding specified for the new database must be compatible with the chosen locale settings (LC_COLLATE and LC_CTYPE). If the locale is C (or equivalently POSIX), then all encodings are allowed, but for other locale settings there is only one encoding that will work properly. [...]

The encoding and locale settings must match those of the template database, except when template0 is used as template.

You should be able to use to create your database by either (or both):

• specifying template0 as your template (instead of the default template1)

• specifying a correct LC_COLLATE (try to use LC_COLLATE = 'C')

• specifying a correct LC_CTYPE = (try to use LC_CTYPE = 'C' also)

You should add these settings to your CREATE statement (and PgAdmin lets you do it from the main form window)

If you don't know about template0/template1 and want to know, read here