Как поменять кодировку в sql shell

PostgreSQL — Кириллица в psql под Windows

В статье пойдёт речь о том, как добиться корректного вывода кириллицы в «консоли» Windows ( cmd.exe ).

Содержание

Описание проблемы

В дистрибутив PostgreSQL, помимо всего прочего, для работы с СУБД входит:

• приложение с графическим интерфейсом pgAdmin ;
• консольная утилита psql .

При работе с psql в среде Windows пользователи всегда довольно часто сталкиваются с проблемой вывода кириллицы. Например, при отображении результатов запроса к таблице, в полях которых хранятся строковые данные на русском языке.

Ну и зачем тогда работать с psql , кому нужно долбить клавиатурой в консольке, когда можно всё сделать красиво и быстро в pgAdmin ? Ну, не всегда pgAdmin доступен, особенно если речь идёт об удалённой машине. Кроме того, выполнение SQL-запросов в текстовом режиме консоли — это +10 к хакирству.

Решение проблемы

• MS Windows 7 SP1 x64;
• PostgreSQL 8.4.12 x32.

На сервере имеется БД, созданная в кодировке UTF8.

Суть проблемы в том, что cmd.exe работает (и так будет до скончания времён) в кодировке CP866 , а сама Windows — в WIN1251 , о чём psql предупреждает при начале работы:

Значит, надо как-то добиться, чтобы кодировка была одна.

В разных источниках встречаются разные рецепты, включая правку реестра и подмену файлов в системных папках Windows. Ничего этого делать не нужно, достаточно всего трёх шагов:

1. сменить шрифт у cmd.exe ;
2. сменить текущую кодовую страницу cmd.exe ;
3. сменить кодировку на стороне клиента в psql .

Конкретные действия

Супер быстро и просто

Запускаете cmd.exe , оттуда psql :

Быстро и просто

Запускаете cmd.exe , оттуда psql :

Вводите пароль (если установлен) и выполняете команду:

И всё. Теперь результаты запроса, содержащие кириллицу, будут отображаться нормально. Но есть небольшой косяк:

Потому предлагаем ещё способ, который этого недостатка лишён.

Посложнее и подольше

Запустить cmd.exe , нажать мышью в правом левом верхнем углу окна, там Свойства — Шрифт — выбрать Lucida Console. Нажать ОК.

В ответ выведет:

Кстати, обратите внимание — теперь предупреждения о несовпадении кодировок нет.

Как поменять кодировку в sql shell

An important restriction, however, is that each database’s character set must be compatible with the database’s LC_CTYPE (character classification) and LC_COLLATE (string sort order) locale settings. For C or POSIX locale, any character set is allowed, but for other libc-provided locales there is only one character set that will work correctly. (On Windows, however, UTF-8 encoding can be used with any locale.) If you have ICU support configured, ICU-provided locales can be used with most but not all server-side encodings.

24.3.1. Supported Character Sets

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

Table 24.1. PostgreSQL Character Sets

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.

24.3.2. Setting the Character Set

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

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:

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:

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.

Important

On most modern operating systems, PostgreSQL can determine which character set is implied by the LC_CTYPE setting, and it will enforce that only the matching database encoding is used. On older systems it is your responsibility to ensure that you use the encoding expected by the locale you have selected. A mistake in this area is likely to lead to strange behavior of locale-dependent operations such as sorting.

PostgreSQL will allow superusers to create databases with SQL_ASCII encoding even when LC_CTYPE is not C or POSIX . As noted above, SQL_ASCII does not enforce that the data stored in the database has any particular encoding, and so this choice poses risks of locale-dependent misbehavior. Using this combination of settings is deprecated and may someday be forbidden altogether.

24.3.3. Automatic Character Set Conversion Between Server and Client

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:

Using the \encoding command in psql . \encoding allows you to change client encoding on the fly. For example, to change the encoding to SJIS , type:

libpq (Section 34.11) has functions to control the client encoding.

Using SET client_encoding TO . Setting the client encoding can be done with this SQL command:

Also you can use the standard SQL syntax SET NAMES for this purpose:

To query the current client encoding:

To return to the default encoding:

Using PGCLIENTENCODING . If the environment variable PGCLIENTENCODING is defined in the client’s environment, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

Using the configuration variable client_encoding. If the client_encoding variable is set, that client encoding is automatically selected when a connection to the server is made. (This can subsequently be overridden using any of the other methods mentioned above.)

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.

24.3.4. Available Character Set Conversions

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.)

Table 24.2. Built-in Client/Server Character Set Conversions

Table 24.3. All Built-in Character Set Conversions

[a] The conversion names follow a standard naming scheme: The official name of the source encoding with all non-alphanumeric characters replaced by underscores, followed by _to_ , followed by the similarly processed destination encoding name. Therefore, these names sometimes deviate from the customary encoding names shown in Table 24.1.

24.3.5. Further Reading

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

Contains detailed explanations of EUC_JP , EUC_CN , EUC_KR , EUC_TW .

The web site of the Unicode Consortium.

Submit correction

If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.

How to change PostgreSQL database encoding to UTF8

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.

Checking the current server encoding.
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.

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

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

PostgreSQL — некоторые особенности базы данных и кодировок в ней

Admin 04.10.2020 , обновлено: 05.11.2021 PostgreSQL

Немного более подробно о кодировках в реляционной базе данных PostgreSQL.

Базы данных по умолчанию

Если мы только установили PostgreSQL, то там увидим такой список баз данных (List of databases):

В зависимости от установленной операционной системы в колонках encoding, collate и ctype можно увидеть разные кодировки.

Пока нас интересуют названия баз данных: template1 и template0.

Эти базы данных являются шаблонами для создания новых баз данных. Другими словами: по образу и подобию их будут созданы позже другие базы данных.

template0 — это шаблон базы данных с первоначальными настройками. Эту БД стоит оставить такой какая она есть.

template1 — этот шаблон используется по умолчанию при создании новых баз данных. Здесь мы можем менять атрибуты, которые потом будут заимствованы для новых баз данных созданных по этому шаблону.

Проблема кодировок

Различия в кодировках

Для того чтобы мигрировать данные с локальной базы данных на базу данных на сервере или наоборот кодировки должны быть одинаковыми. В противном случае ошибка будет примерно такого плана:

Command was: INSERT INTO public.operations (id, user_id) VALUES (333, 1);
pg_restore: error: could not execute query: ERROR: character with byte sequence 0xd0 0x94 in encoding «UTF8» has no equivalent in encoding «LATIN1»

Разная сортировка в одинаковых кодировках

Даже если кодировки одинаковые, это всё равно может приводить к отличным результатам при сортировке. Автор по ссылке выше утверждает, что кодировка lc_collate = C решает эти проблемы.

Меняем кодировки LATIN1 на UTF8

Сначала разберем пару ошибок, которые могут возникать в процессе.

Эта ошибка возникнет, если пропустить первую команду и сразу попробовать переключиться на базу данных template0:

postgres-# \c template0
FATAL: database «template0» is not currently accepting connections
Previous connection kept

У следующей ошибки сложная история, но её избежим, когда принудительно укажем значения в LC_CTYPE и LC_COLLATE.

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

Теперь определим несколько базовых понятий.

LC_COLLATE — порядок сортировки строк
LC_CTYPE — классификация символов

Ниже команды смены локали для базы данных template1. Вместо ‘C’ можно использовать и другие значения, например: «en_US.UTF-8».

Сначала идет команда, если её выполнение будет успешным, то будет выведено значение такое же как указано здесь следом. Если ничего не выводится или выводятся другие сообщения, значит команды введены неправильно или с ошибками.

update pg_database set datallowconn = TRUE where datname = ‘template0’ ;
UPDATE 1

\c template0
You are now connected to database "template0" .

update pg_database set datistemplate = FALSE where datname = ‘template1’ ;
UPDATE 1

drop database template1;
DROP DATABASE

При создании таблицы делаем её с кодировкой UTF8 и сортировкой C.

Важно для русской кириллицы, чтобы корректно работал поиск по базе данных, создавать такую:

Вариант без указания языка:

Или с en_US.UTF-8:

Продолжаем выполнять команды:

update pg_database set datistemplate = TRUE where datname = ‘template1’ ;
UPDATE 1

\c template1
You are now connected to database "template1" .

update pg_database set datallowconn = FALSE where datname = ‘template0’ ;
UPDATE 1

Теперь когда мы будем создавать таблицы они сразу будут у нас в нужной кодировке.

Альтернативный вариант

Этот вариант хоть и проще, но технически-правильно править эталонную таблицу, а на основании её уже создавать следующие таблицы. Однако можно обойтись и без этого и принудительно каждый раз создавать БД с нужными кодировками:

Результат будет таким же, как и в случае, если мы поменяли локаль у таблицы template1 и далее вне сеанса psql ввели команду:

Тут можно заметить что пользователь postgres, а не ploshadka, но это легко поправимо, если создавать с указанием пользователя:

Альтернативный вариант всем вариантам

Проблема с кодировками могла и не возникать, если до установки PostgreSQL в самой системе заранее указать нужную кодировку — изменение локали на сервере.

Читайте также

У сайта нет цели самоокупаться, поэтому на сайте нет рекламы. Но если вам пригодилась информация, можете лайкнуть страницу, оставить комментарий или отправить мне подарок на чашечку кофе.