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

Admin PostgreSQL

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

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

Введем команду:

\list

Если мы только установили 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.

CREATE DATABASE template1 WITH template = template0 ENCODING 'UTF8' LC_CTYPE 'C' LC_COLLATE 'C';
CREATE DATABASE

Или с en_US.UTF-8:

CREATE DATABASE template1 WITH template = template0 ENCODING 'UTF8' LC_CTYPE 'en_US.UTF-8' LC_COLLATE 'en_US.UTF-8';
CREATE DATABASE

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

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

Проверим результат:

\list

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

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

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

CREATE DATABASE ploshadka_db_1 WITH owner=ploshadka ENCODING = 'UTF-8' lc_collate = 'C' lc_ctype = 'C' template template0;

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

createdb ploshadka_db_2

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

createdb ploshadka_db_3 --owner ploshadka

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

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

Метки:

Если вам пригодилась информация, вы можете поблагодарить автора сайта символическим пожертвованием:

Добавить комментарий

Напишите свой комментарий, если вам есть что добавить/поправить/спросить по теме текущей статьи:
"PostgreSQL — некоторые особенности базы данных и кодировок в ней"