Postgres collate example in select?

Question

I would like to comprehend the different behavior of like and =. Sadly, I can not reproduce the following simple example thus that both queries return a different result:

SELECT 'ä' LIKE 'ae' COLLATE "de_DE.utf8"; 
SELECT 'ä' = 'ae' COLLATE "de_DE.utf8"; 

both return false.

What am I doing wrong?

• user1822: I don't think de_DE.utf8 has any rules about the fact that ä and ae should be considered equivalent. If at all, this might be possible with an ICU collation (but I don't know): postgresql.org/docs/current/static/…
• Stefan K.: I sadly haven't compiled my postgres with ICU support. This means that without ICU collation, like and = behave identical, if no wildcards are used?
• Patrick Mevzek: This other answer has useful information: stackoverflow.com/a/11007216/6368697 pointing to the unaccent module.
• Erwin Brandstetter: @PatrickMevzek But note that unaccent() cannot help with 'ä' = 'ae'. It can help with 'ä' = 'a'.

Answer 1

PostgreSQL does not support = or LIKE on COLLATE. This is because internally index ordering uses = and so even if the collation returns that they're equal PostgreSQL falls back to binary equal. This is documented,

Note that while this system allows creating collations that "ignore case" or "ignore accents" or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

PostgreSQL also doesn't support Unicode collation in character classes.

• Peter Eisentraut: This is changed in PostgreSQL 12. See the updated documentation about "nondeterministic collations".