Correction database encoding of PostgreSQL from LATIN1 to UTF8

PostreSQL, UTF8, LATIN1, Ubuntu

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)

We recommend hosting TIMEWEB
We recommend hosting TIMEWEB
Stable hosting, on which the social network EVILEG is located. For projects on Django we recommend VDS hosting.
Support the author Donate

Comments

Only authorized users can post comments.
Please, Log in or Sign up
How to become an author?

Contribute to the evolution of the EVILEG community.

Learn how to become a site author.

Learn it
Donate

Good day, Dear Users!!!

I am Evgenii Legotckoi, developer of EVILEG. And it is my hobby project, which helps to learn programming another programmers and developers

If the site helped you, and you want also support the development of the site, than you can donate by following ways

PayPalYandex.Money
Timeweb

Let me recommend you the excellent hosting on which EVILEG is located.

For many years, Timeweb has been proving his stability.

For projects on Django I recommend VDS hosting

View Hosting Timeweb
s
June 3, 2020, 1:56 a.m.
silo1995

C++ - Тест 003. Условия и циклы

  • Result:35points,
  • Rating points-10
AP
June 2, 2020, 9:11 p.m.
Aleksej Pikenin

C++ - Test 005. Structures and Classes

  • Result:75points,
  • Rating points2
June 2, 2020, 1:04 p.m.
Daniil Chizhevskij

C++ - Test 001. The first program and data types

  • Result:86points,
  • Rating points6
Last comments
May 31, 2020, 8:15 a.m.
IscanderChe

Как установить OpenCV на Qt под Windows

Добавлю от себя: на Windows 10 x64 с MinGW 7.3.0 в CMake надо установить флаг OPENCV_ENABLE_ALLOCATOR_STATS=OFF, тогда всё скомпилится нормально.
May 29, 2020, 1 p.m.
Evgenij Legotskoj

Django - Tutorial 023. Like Dislike system using GenericForeignKey

Думал так, но похоже что нет. {{ post.votes.likes.user.username }} Это же QuerySet будет, а не отдельный единственный объект {% for vote in post.votes %} {{ vote.user.username …
May 29, 2020, 11:43 a.m.
Vladislav Melenchuk

Django - Tutorial 023. Like Dislike system using GenericForeignKey

А как получить имя пользователя, который поставил лайк? Думал так, но похоже что нет. {{ post.votes.likes.user.username }}
May 29, 2020, 6:30 a.m.
Evgenij Legotskoj

Qt/C++ - Lesson 039. How to paint stroke in QSqlTableModel by value in the column?

У меня работает. Исправлял в проекте, который приложен к статье. А что происходит в вашем коде, с учётом места вызова этого кода, я знать не могу ;) Дебажьте и добавляйте условия, кото…
Now discuss on the forum
June 3, 2020, 2:34 a.m.
Evgenij Legotskoj

Qt- C++ QTableView подсветить строку

Некогда было
f
June 3, 2020, 1:49 a.m.
fryn3

Можно ли сделать в QML таблицу как в Excel?

edi-tableview - нашел пока такое выглядит коряво, посмотрим что можно сделать
June 2, 2020, 2:46 a.m.
Evgenij Legotskoj

Медиа файлы Google Firebase

Картинки можете попробовать сжимать через QPixmap, там есть возможность установки scaleFactor, через него можете устанавливать нужные параметры. А что касается конвертации видео, то лучше п…
June 2, 2020, 2:01 a.m.
Evgenij Legotskoj

Перехват обращения к локальным файлам QWebEngineView

В вашем случае вполне адекватное решение. Так сказать меньше зло. В противном случае пришлось бы очень много переписывать и перепиливать.
a
June 1, 2020, 10:26 a.m.
alekseyttrv

SSL на Android

у меня стоит версия Qt 5.14.2. В настройках android поставил openssl из коробки, и этот прроект автоматически стянулся. Достаточно было только добавить в .pro-файл строку после этого и все …
About
Services
© EVILEG 2015-2020
Recommend hosting TIMEWEB