Нормализация Юникода в Postgres

Question

У меня есть большое количество шотландских и валлийских акцентированных географических названий (сочетающих могилу, острый, диафрагму и диафрагмы), которые мне нужно обновить до их унифицированной юникодовой формы, например, более короткой формы 00E1 (\xe1) для á вместо 0061 + 0301 (\x61\x301)

Я нашел решение из старого почтового списка Postgres 2009, используя pl/python,

create or replace function unicode_normalize(str text) returns text as $$
  import unicodedata
  return unicodedata.normalize('NFC', str.decode('UTF-8'))
$$ LANGUAGE PLPYTHONU;

Это работает, как и ожидалось, но заставило меня задуматься, есть ли способ сделать это напрямую с помощью встроенных функций Postgres. Я пробовал различные преобразования, используя convert_to, все тщетно.

РЕДАКТИРОВАТЬ: Как указал Крейг, и одна из вещей, которые я пытался:

SELECT convert_to(E'\u00E1', 'iso-8859-1');

возвращается \xe1, в то время как

SELECT convert_to(E'\u0061\u0301', 'iso-8859-1');

терпит неудачу с ERROR: character 0xcc81 of encoding "UTF8" has no equivalent in "LATIN1"

Answer 1

Я думаю, что это ошибка Pg.

На мой взгляд, PostgreSQL должен нормализовать utf-8 в предварительно составленную форму перед выполнением преобразований кодирования. Результат показанных конверсий неверен.

Я подниму это на pgsql-багах... готово.

http://www.postgresql.org/message-id/[e-mail]

Вы должны быть в состоянии следить за темой там.

Редактировать: pgsql-хакеры, похоже, не согласны, поэтому вряд ли это изменится в спешке. Я настоятельно советую вам нормализовать ваш UTF-8 на границах вашего приложения.

Кстати, это может быть упрощено до:

regress=> SELECT 'á' = 'á';
 ?column? 
----------
 f
(1 row)

что просто сумасшедший разговор, но разрешен. Первый составлен заранее, второй нет. (Чтобы увидеть этот результат, вам нужно будет скопировать и вставить, и он будет работать только в том случае, если ваш браузер или терминал не нормализуют utf-8).

Если вы используете Firefox, возможно, вы не правильно видите вышеперечисленное; Хром отрисовывает это правильно. Вот что вы должны увидеть, если ваш браузер правильно обрабатывает декомпозированный Unicode:

Answer 2

PostgreSQL 13 представил строковую функцию , который доступен, если используется кодировка сервера UTF8.

      > select 'päivää' = 'päivää' as without, normalize('päivää') = normalize('päivää') as with_norm ;
 without | with_norm
---------+-----------
 f       | t
(1 row)

Обратите внимание, что я ожидаю, что это пропустит какие-либо индексы, и поэтому слепое использование этого в горячем производственном запросе может стать рецептом катастрофы.

Отличные новости для нас, которые наивно хранят имена файлов NFD от пользователей Mac в наших базах данных.