How to Create a PostgreSQL database with different Encoding?

Question

I want to create a New Database that will store data in German(i.e Text with accents etc). Hence I tried to create it using the following SQL:

CREATE DATABASE testdb
  WITH ENCODING='LATIN1'
       OWNER=postgres
       TEMPLATE=template0
       CONNECTION LIMIT=-1;

When I run this on my Windows Development machine, I get the following error message:

ERROR: encoding "LATIN1" does not match locale "English_United States.1252" DETAIL: The chosen LC_CTYPE setting requires encoding "WIN1252".

On my Linux server, I get the following error message:

ERROR: encoding "LATIN1" does not match locale "en_US.UTF-8" DETAIL: The chosen LC_CTYPE setting requires encoding "UTF8".

What do I need to do to create a Database which can correctly store German text?

Answer 1

On Linux, I was able to create a database with encoding LATIN1 by 1st initializing the database using initdb in \usr\pgsql-10\bin as initdb --encoding=en_US.iso88591. Read more about localization and available character set support.

Then restart the service using-

systemctl restart postgresql-10

This will re-initialize the template databases template0 and template1 with the new encoding and then you can create the database using

createdb <dbname>

Hope this helps.

Answer 2

Use the site below for choose your character, then use example:

http://pgdocptbr.sourceforge.net/pg82/multibyte.html#CHARSET-TABLE

postgres=# CREATE DATABASE database_name 
          WITH TABLESPACE tablespace_name TEMPLATE = template0 
          OWNER = database_user ENCODING 'WIN1252' 
         LC_CTYPE = 'Portuguese_Brazil.1252' LC_COLLATE = 'Portuguese_Brazil.1252';

CREATE DATABASE