begin; CREATE OR REPLACE FUNCTION public.insensitive_query(text) RETURNS text AS $func$ SELECT lower(public.unaccent('public.unaccent', CAST($1 AS text))) $func$ LANGUAGE sql IMMUTABLE; ALTER FUNCTION public.insensitive_query(text) OWNER TO [your user that has create rights on the database]; commit;
To use in a query:
WHERE insensitive_query(some_table.name) LIKE insensitive_query('%Étranger%')
What you can do is storing the unaccented, simplified version of a word. Which I'm sure pg already does in a way.
[0] https://www.postgresql.org/docs/12/indexes-expressional.html
CREATE INDEX index_name ON some_table (public.insensitive_query(NAME));
Thank you and I feel a little sheepish I didn't do it first!
strange mix of assumptions there, since searching text strings is Job One of computer search since 'forever' .. if Lucene has done some (search) things well, there is no reason that others do not have a chance to also do those things well. In fact, encourage it, foster it, engage with the process that brings new software capacities ..
Those who wanted Unicode-compliant collation support had already installed it years ago.
For the many simple cases where a grep-like search is enough, my little experience with pg was far from satisfying. MySQL is simple to use for this thanks to the awfully named `utf8_mb4_unicode_ci`. With Postgres, searching for "étranger" won't find "Etranger" and "Étranger" unless you put some hard work into your database search system.
Unfortunately, it seems that PG12 won't change much in that aspect. From the link above, you have to create the collation (which is already a heavy restriction) and then "certain operations are not possible with nondeterministic collations, such as pattern matching operations." Looks like uncharted territory.
On a side note, I've always seen Solr, SphinxSearch and such specialized tools used instead of the DB's FTS. The main drawback is keeping the data in sync, but it has many pros.