When storing a domain in punycode, postgresql loses the ability to match the LIKE operator by the original, non-encoded value. For example, if we encode smth like примерен.site (example in Bulgarian), we get xn--e1aahqhhhd.site in our database.
It would seem that translating part of the search word into punycode before querying the database should solve the problem, but the translation of part of the source string (примерен.site) does not match the corresponding part of the encoded one.
For example, примере = xn--e1aahqngd, not xn--e1aahqhhh. Accordingly, примере, translated in punycode will not return proper result when requesting:
SELECT * FROM domains where domains.domain LIKE '%xn--e1aahqngd%';
I've tried various postgresql built-in functions like envelope or decode, but they don't support punycode.
Something like
SELECT * FROM domains where domains.domain LIKE '% (punycode(примере) ) %';
would be perfect, but I couldn't find it.
The simple answer is to create another column (or table in postgresql) where the original unencoded domain name will be stored and LIKE search will be possible on them, but I hope that I am not the first to encounter this or a similar problem and there will be people who will share their experience of solving)
P.S.: My api is written in Yii2, maybe there is a tool in it there that could help.
