Postgres中的Unicode规范化

Question

我有大量带有苏格兰和威尔士口音的地名(包括重音、尖音符、抑扬符和分音符)，需要将它们更新为它们的Unicode标准化形式，例如，使用短格式00E1 (\xe1)代替0061 + 0301 (\x61\x301)来表示á。

我在一个2009年的Postgres nabble邮件列表中找到了一个解决方案，使用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进行转换的方法，但都徒劳无功。

编辑：正如Craig指出的那样，这是我尝试过的其中一件事情：

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

返回\xe1，而

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

使用UTF8编码的字符0xcc81在LATIN1中没有相应的字符，因此出现错误：ERROR: character 0xcc81 of encoding "UTF8" has no equivalent in "LATIN1"

- John Powell

---

哦，有趣。我原本以为Pg会在convert_to之前进行组成和规范化，但实际上它并没有。这似乎是一个错误，因为这意味着convert_to(E'\u0061\u0301', 'iso-8859-1')会失败，但convert_to(E'\u00E1', 'iso-8859-1')会成功。 - Craig Ringer

@Craig，是的，回答第一个评论。是的，我尝试了那些转换，并得到了“在LATIN1中没有等价物”的错误。如果你认为这是一个bug，能否把它作为一个答案发布，这样我就可以接受它了？ - John Powell

让我们先等待并观望是否有其他人有想法。 - Craig Ringer

@CraigRinger，好主意。我很高兴在convert_to函数中没有错过任何明显的东西。 - John Powell

@CraigRinger。这个问题已经过去了一段时间，您是否仍然认为这是一个错误？ - John Powell

Answer 1

我认为这是Pg的一个bug。

在我看来，在执行编码转换之前，PostgreSQL应该将UTF-8规范化为预组合形式。所示转换的结果是错误的。

我会在pgsql-bugs上提出来...已经完成。

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

你可以在那里跟进这个线程。

编辑：pgsql-hackers似乎不同意，所以这不太可能很快改变。强烈建议您在应用程序输入边界规范化您的UTF-8。

顺便说一句，这可以简化为：

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

这种说法完全是荒谬的，但是却被允许。第一个是预组合的，第二个不是。（要看到这个结果，你需要复制并粘贴，而且它只在你的浏览器或终端不规范化UTF-8时才能正常工作）。 如果您使用的是Firefox，则可能无法正确显示上述内容；Chrome会正确呈现。如果您的浏览器可以正确处理分解的Unicode，则应该看到以下内容：

- Craig Ringer

---

我花了很多时间尝试规范化Unicode中的异常情况，但从未能够正确地解决它。NFD、NFC、NFKD、NFKC？每个都解决了一些问题并创建了新问题。我开始相信UNICODE本身的概念存在严重缺陷。欺骗人眼的方式太多，而没有适当的规范化方法。 - Erwin Brandstetter

我倾向于同意@ErwinBrandstetter的观点。真的需要有一种统一的方法来完成事情。看看Tatsuo Ishii关于组合排除字符的帖子吧。糟糕透了。唉，那艘船已经开走了。 - Craig Ringer

@CraigRinger。感谢您的提交。 - John Powell

@ErwinBrandstetter。我很高兴不是唯一一个被Unicode搞得晕头转向的人。尽管在计算机中表达所有已知的书写方案并不是一件轻松的事情，但它有时似乎过于复杂了，显然 :D - John Powell

我在这一点上不同意汤姆的看法（这种情况经常发生）。然而，事实是没有简单的解决方法来解决这个错误。 - Craig Ringer

Answer 2

PostgreSQL 13引入了字符串函数normalize(text [, form]) → text，仅在服务器编码为UTF8时可用。

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

请注意，我预计这会忽略任何索引，因此在热生产查询中盲目使用可能会导致灾难。

对于我们那些天真地将Mac用户的NFD文件名存储在数据库中的人来说，这是一个好消息。

- borellini