Как изменить кодировку postgresql

Table 24.1 shows the character sets available for use in PostgreSQL.

Not all client APIs support all the listed character sets. For example, the PostgreSQL JDBC driver does not support MULE_INTERNAL, LATIN6, LATIN8, and LATIN10.

The SQL_ASCII setting behaves considerably differently from the other settings. When the server character set is SQL_ASCII, the server interprets byte values 0–127 according to the ASCII standard, while byte values 128–255 are taken as uninterpreted characters. No encoding conversion will be done when the setting is SQL_ASCII. Thus, this setting is not so much a declaration that a specific encoding is in use, as a declaration of ignorance about the encoding. In most cases, if you are working with any non-ASCII data, it is unwise to use the SQL_ASCII setting because PostgreSQL will be unable to help you by converting or validating non-ASCII characters.

initdb defines the default character set (encoding) for a PostgreSQL cluster. For example,

initdb -E EUC_JP

sets the default character set to EUC_JP (Extended Unix Code for Japanese). You can use --encoding instead of -E if you prefer longer option strings. If no -E or --encoding option is given, initdb attempts to determine the appropriate encoding to use based on the specified or default locale.

You can specify a non-default encoding at database creation time, provided that the encoding is compatible with the selected locale:

createdb -E EUC_KR -T template0 --lc-collate=ko_KR.euckr --lc-ctype=ko_KR.euckr korean

This will create a database named korean that uses the character set EUC_KR, and locale ko_KR. Another way to accomplish this is to use this SQL command:

CREATE DATABASE korean WITH ENCODING 'EUC_KR' LC_COLLATE='ko_KR.euckr' LC_CTYPE='ko_KR.euckr' TEMPLATE=template0;

Notice that the above commands specify copying the template0 database. When copying any other database, the encoding and locale settings cannot be changed from those of the source database, because that might result in corrupt data. For more information see Section 23.3.

The encoding for a database is stored in the system catalog pg_database. You can see it by using the psql -l option or the l command.

$ psql -l
                                         List of databases
   Name    |  Owner   | Encoding  |  Collation  |    Ctype    |          Access Privileges
-----------+----------+-----------+-------------+-------------+-------------------------------------
 clocaledb | hlinnaka | SQL_ASCII | C           | C           |
 englishdb | hlinnaka | UTF8      | en_GB.UTF8  | en_GB.UTF8  |
 japanese  | hlinnaka | UTF8      | ja_JP.UTF8  | ja_JP.UTF8  |
 korean    | hlinnaka | EUC_KR    | ko_KR.euckr | ko_KR.euckr |
 postgres  | hlinnaka | UTF8      | fi_FI.UTF8  | fi_FI.UTF8  |
 template0 | hlinnaka | UTF8      | fi_FI.UTF8  | fi_FI.UTF8  | {=c/hlinnaka,hlinnaka=CTc/hlinnaka}
 template1 | hlinnaka | UTF8      | fi_FI.UTF8  | fi_FI.UTF8  | {=c/hlinnaka,hlinnaka=CTc/hlinnaka}
(7 rows)

PostgreSQL supports automatic character set conversion between server and client for many combinations of character sets (Section 24.3.4 shows which ones).

To enable automatic character set conversion, you have to tell PostgreSQL the character set (encoding) you would like to use in the client. There are several ways to accomplish this:

If the conversion of a particular character is not possible — suppose you chose EUC_JP for the server and LATIN1 for the client, and some Japanese characters are returned that do not have a representation in LATIN1 — an error is reported.

If the client character set is defined as SQL_ASCII, encoding conversion is disabled, regardless of the server’s character set. (However, if the server’s character set is not SQL_ASCII, the server will still check that incoming data is valid for that encoding; so the net effect is as though the client character set were the same as the server’s.) Just as for the server, use of SQL_ASCII is unwise unless you are working with all-ASCII data.

PostgreSQL allows conversion between any two character sets for which a conversion function is listed in the pg_conversion system catalog. PostgreSQL comes with some predefined conversions, as summarized in Table 24.2 and shown in more detail in Table 24.3. You can create a new conversion using the SQL command CREATE CONVERSION. (To be used for automatic client/server conversions, a conversion must be marked as “default” for its character set pair.)

These are good sources to start learning about various kinds of encoding systems.

Содержание

• 1 Описание проблемы
• 2 Решение проблемы
  • 2.1 Суть
  • 2.2 Конкретные действия
    • 2.2.1 Супер быстро и просто
    • 2.2.2 Быстро и просто
    • 2.2.3 Посложнее и подольше

1. 1. Проверка кодировки
2. 2. Исправление кодировки для template1

How to change PostgreSQL database encoding to UTF8

9th October, 2019

The default encoding of the template databases in PostgreSQL is set to SQL_ASCII. If this encoding has not been changed, then the new databases will be created using this template and hence will have the same encoding SQL_ASCII. Overall this should not be a big problem unless Unicode data is required to be saved in the database. In that case you will receive an error such as :

Unicode escape values cannot be used for code point values above 007F when the server encoding is not UTF8.

In this blog, we will go through all steps in order to change the encoding of the database to UTF8. First of all one needs to know that encoding of already created database cannot be altered. Hence we need to drop and create a new one.

1. Checking the current server encoding.

   postgres@yourserver:~$ psql
   psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1))
   Type "help" for help.
   
   postgres=# SHOW SERVER_ENCODING;
    server_encoding
   -----------------
    SQL_ASCII
   (1 row)
   
   postgres=#c your_database;
   You are now connected to database "your_database" as user "postgres".
   your_database=# SHOW SERVER_ENCODING;
    server_encoding
   -----------------
    SQL_ASCII
   (1 row)
   

   In this case, as you can see, both the template database (the first result) and your_database is based on SQL_ASCII encoding. 
   There are two ways to proceed from here but before that its necessary to understand the process of CREATE DATABASE command. This command by default creates a copy of database from template1 which is the default template for creating any new database. This template has some preset settings already and ready for creating new databases. There is also another template which is free from all preset settings called template0 [ref.https://www.postgresql.org/docs/12/manage-ag-templatedbs.html]. 

   Coming back to ways of creating new database, one can either create new database using template0 and applying encoding UTF8 to it. Or updating the template1 encoding to UTF8, thereby any future databases will also be based on UTF8 as well. From here on, we will follow the second solution which is better in my opinion.

2. Create backup of your_database.

   postgres@yourserver:~$ pg_dump your_database > dump.sql​

   OPTIONAL: On pg_dump, sequences by default are set to AS integer. This might cause issues for some applications (such as Django). To fix this, use sed 's/AS integer//' dump.sql > altered_dump.sql

3. Drop your database.

   postgres@yourserver:~$ dropdb your_database​

4. Update the template1 encoding to UTF8.

   postgres@yourserver:~$ psql
   psql (10.7 (Ubuntu 10.7-0ubuntu0.18.04.1))
   Type "help" for help.
   
   postgres=# UPDATE pg_database SET datistemplate = FALSE WHERE datname = 'template1';
   postgres=# DROP DATABASE template1;
   postgres=# CREATE DATABASE template1 WITH TEMPLATE = template0 ENCODING = 'UTF8';
   postgres=# UPDATE pg_database SET datistemplate = TRUE WHERE datname = 'template1';
   postgres=# c template1;
   You are now connected to database "template1" as user "postgres".
   template1=# VACUUM FREEZE;

5. Create a new database having the same name as before. This time it will use the newly updated template1 for creating the database.

   postgres@yourserver:~$ createdb -E utf8 your_database​

6. Restore your data using your backup file (either the dump.sql or altered_dump.sql file)

   postgres@yourserver:~$ psql your_database < dump.sql​

   That’s it. Now you may check the encoding again for the new database by following the step 1 again .