5

Postgres 13

I am looking for a way to search UTF-8 text that may have variant character representations ( what is the proper term for this? ie π‹πˆπ…π„ vs life ) within postgresql.

I am running into issues matching variant characters, consider

-- This works as expected
select 'life' ilike '%life%' matches 

-- I would like to also be able to match against this source text like this
select 'π‹πˆπ…π„' ilike '%life%' matches

Note that there are myriad variants, I do not particularly care about matching against non-ascii representable characters at this point, that is I think I would be fine with a lossy conversion from utf-8 to ascii as long as π‹πˆπ…π„ would end up matching with life.

2
  • 1
    What is the question ? How to store it, How to search for it ? How to design the database for it ? Jan 5, 2023 at 12:10
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Jan 5, 2023 at 12:10

1 Answer 1

4

π‹πˆπ…π„ vs LIFE is a case of Unicode "compatibility equivalence", which is defined in UAX#15 as:

Compatibility equivalence is a weaker type of equivalence between characters or sequences of characters which represent the same abstract character (or sequence of abstract characters), but which may have distinct visual appearances or behaviors. The visual appearances of the compatibility equivalent forms typically constitute a subset of the expected range of visual appearances of the character (or sequence of characters) they are equivalent to

Explicit normalization approach

To normalize for compatibility equivalence testing, the NFKC or NFKD forms can be used.

Postgres 13 or newer provides the normalize function for that:

=> select normalize('π‹πˆπ…π„', nfkc);
 normalize 
-----------
 LIFE

Comparing the lowercase life to the uppercase LIFE happens at a different level, which is of course more widely known. ilike may be used as in the question, or both strings can be converted to the same case with lower or upper.

Collation approach

Another approach is to use a non-deterministic ICU collation (Postgres 12 or newer) that compares at the "secondary level". At this level, the compatibility equivalence and the case equivalence are taken into account directly by the collation:

=> CREATE COLLATION nd2 (
  provider = 'icu',
  locale = '@colStrength=secondary', -- or 'und-u-ks-level2'
  deterministic = false
);

=> select 'life' = 'π‹πˆπ…π„' collate "nd2";
 ?column? 
----------
 t

Such collations cannot be used with the LIKE operator, though.

1
  • Absolutely fantastic answer and background information - thank you so much! Keep being awesome.
    – unomi
    Jan 5, 2023 at 20:37

Your Answer

By clicking β€œPost Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.