PostgreSQL's unaccent function is unable to remove accents (diacritic signs) from Japanese characters like 'ド'

Question

The postgresql out of box unaccent function is unable to remove accents (diacritic signs) with more then one diacritic in a character. The character 'ド' which is failing for normalisation have 2 diacritic signs in it.

I tried to check the latest version ofhttps://github.com/postgres/postgres/blob/master/contrib/unaccent/unaccent.rules but still it does not have mapping for the Japanese characters like 'ド' . Can someone please guide how we can resolve this issue.

Answer 1

Strings can be decomposed with Unicode rules by normalize(text, NFD) since PostgreSQL 13. In the case of the mentioned string, it produces the following code points:

SELECT
  codepoint, 
  to_hex(ascii(codepoint))
FROM
  regexp_split_to_table(normalize('ド', NFD), '') AS codepoint;

 codepoint | to_hex 
-----------+--------
 ト        | 30c8
 ゙          | 3099

U+3099 is Combining Katakana-Hiragana Voiced Sound Mark, which is in the Hiragana block. According to this decomposition, there is no diacritical sign in that string, otherwise the code point would be in one of these blocks:

• U+0300 - U+036F Combining Diacritical Marks
• U+1AB0 - U+1AFF Combining Diacritical Marks Extended
• U+1DC0 - U+1DFF Combining Diacritical Marks Supplement
• U+20D0 - U+20FF Combining Diacritical Marks for Symbols

If for whatever reason you disagree with that, you may either customize unaccent.rules to add your own translations, or if that's impractical, use instead of unaccent() your own function like the following, based on the NFD decomposition:

CREATE FUNCTION remove_diacritics(text) RETURNS text
AS $$
select regexp_replace (
   normalize($1, NFD),
  '[\u0300-\u036f\u1ab0-\u1aff\u1dc0-\u1dff\u20d0-\u20ff]', -- range of code points to remove
  '',
  'g');
$$ LANGUAGE sql STRICT;

Then you can customize the range of code points to remove, according to your local needs.

Answer 2

Like Vérace mentioned in comment, this symbol doesn't have diacritic signs. This is a syllable as Japanese use syllabary instead of alphabet.

That means that while "ト" is "to" in katakana, "ド" is a different syllable: "do".

This is why unaccent.rules does not have such mapping. If you need, for some reason, to remove diacritics like dakuten (this double dot) anyway, you will have to write your own rules for this function. This way you could reduce other syllables to basic 48 gojūon characters.