Correction database encoding of PostgreSQL from LATIN1 to UTF8

PostreSQL, Ubuntu, LATIN1, UTF8

In the process of server settings for the site, I had to face some problems. In particular, the problem of the PostgreSQL database encoding. The fact is that when you install the PostgreSQL, database templates created with the coding LATIN1, and on the Django site works, using UTF8 encoding. As a result, dropped out following error when trying to insert the data:

ERROR: encoding UTF8 does not match locale en_US Detail: The chosen LC_CTYPE setting requires encoding LATIN1.

Search for information, it was possible to find several solutions, including a solution that allows you to re-create the database template with the encoding UTF8. But Walk carefully on the symptoms of the problem.

Check encoding

To check the encoding used on the server and the database, you must execute the following commands.

Go to work mode with PostgreSQL:

sudo -u postgres psql

psql - is a utility for working with databases and postgres - is a super user of PostgreSQL.

And execute the following commands:

postgres=# SHOW SERVER_ENCODING;
 server_encoding 
-----------------
 LATIN1
(1 row)

postgres=# \l
                             List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges   
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | LATIN1   | en_US   | en_US | 
 template0 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
 template1 | postgres | LATIN1   | en_US   | en_US | =c/postgres          +
           |          |          |         |       | postgres=CTc/postgres
(3 rows)

The output will be shown LATIN1 encoding, both for server and data bases.

Correction for encoding template1

Now correct encoding template1, which is used to create a database.

postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
postgres=# DROP DATABASE Template1;
postgres=# CREATE DATABASE template1 WITH owner=postgres ENCODING = 'UTF-8' lc_collate = 'en_US.utf8' lc_ctype = 'en_US.utf8' template template0;
postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';

In this case, we first point out that template1 is not a template for the databases. We delete this template. Then create a new template with the encoding UTF8 and set up this database as a template for new databases. Then the new database will be created with the encoding UTF8.

And check out what we have to be encoded template1 template, the server itself, by the way, will continue to be encoded LATIN1

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | LATIN1   | en_US      | en_US      | 
 template0 | postgres | LATIN1   | en_US      | en_US      | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | 
(3 rows)