PostgreSQL full-text search returns unexpected results with accented characters
I'm experiencing issues with full-text search in PostgreSQL when dealing with accented characters. My table, `articles`, has a `content` column containing text in multiple languages, including some entries with accented characters (like `café`, `jalapeño`, etc.). I've set up a full-text search using the `pg_trgm` extension to handle partial matches, but when I search for `cafe` (without the accent), it does not return results that should match `café`. Here's how I set up my index: ```sql CREATE EXTENSION pg_trgm; CREATE INDEX idx_gin_content ON articles USING gin (content gin_trgm_ops); ``` I'm using the following query to search: ```sql SELECT * FROM articles WHERE content ILIKE '%cafe%'; ``` This query works when the content does not have accents, but when searching for `cafe`, it doesn't return entries like `café`. I expected it to match due to the similarity. I've tried normalizing the text before indexing and also using `unaccent` with the `tsvector`, but it still doesn't yield the expected results. Here’s what I tried with unaccent: ```sql CREATE EXTENSION unaccent; UPDATE articles SET content = unaccent(content); ``` After running that, I re-created the index, but the search still fails to match. I would appreciate any insights on how to make the full-text search more flexible for accented characters. Is there a better way to index or search these entries? Am I missing something in the configuration or formulation of my query? Thanks in advance! This is part of a larger API I'm building. What's the best practice here?