Postgres databases with different encoding

database postgresql encoding multiple-databases
marcio picture marcio · May 10, 2011 · Viewed 7.4k times · Source

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

leonbloy picture leonbloy · May 10, 2011

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

Related questions

How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More
What is this Javascript "require"?

I'm trying to get Javascript to read/write to a PostgreSQL database. I found this project on github. I was able to get the following sample code to run in node. var pg = require('pg'); //native libpq bindings = `var pg = …

Read More
Import SQL dump into PostgreSQL database

We are switching hosts and the old one provided a SQL dump of the PostgreSQL database of our site. Now, I'm trying to set this up on a local WAMP server to test this. The only problem is that I …

Read More