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

can you tell us exactly your CREATE command in sql format (available in PgAdmin in the SQL tab) and the exact error message? — leonbloy
– leonbloy, Commented May 10, 2011 at 20:45
Error message: ERROR: encoding LATIN1 does not match server's locale Portuguese_Brazil_1252 DETAIL: The server's LC_CTYPE setting requires encoding WIN1252. — marcio
– marcio, Commented May 10, 2011 at 21:42

Community · Accepted Answer · 2012-09-27 00:10:30Z

8

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

edited Sep 27, 2012 at 0:10

CommunityBot

11 silver badge

answered May 10, 2011 at 21:37

leonbloy

76.4k22 gold badges149 silver badges197 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

marcio Over a year ago

For a sake of sanity (since I never configured PostGres in windows) I just reinstalled PostGres, choosing the Posix compatible option during setup. Thanks for clarifying that.

Collectives™ on Stack Overflow

Postgres databases with different encoding

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related