News for hackers. Stuff that matters.
Re:The state of full text search in PostgreSQL 12 by irrational on Monday February 03, 2020 @01:49PM (#22227502)
CREATE EXTENSION unaccent;

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%')

Re:The state of full text search in PostgreSQL 12 by rakoo on Monday February 03, 2020 @03:16PM (#22228494)
If you use a function like this, it will need to be run for every entry; doesn't that invalidate the whole point of having indexes ?

What you can do is storing the unaccented, simplified version of a word. Which I'm sure pg already does in a way.

Re:The state of full text search in PostgreSQL 12 by Mister_Snuggles on Monday February 03, 2020 @03:36PM (#22228705)
You can also create an index on the result of the function[0].

[0] https://www.postgresql.org/docs/12/indexes-expressional.html

Re:The state of full text search in PostgreSQL 12 by irrational on Monday February 03, 2020 @04:14PM (#22229185)
I forgot to include the index and it is too late to edit my comment.

CREATE INDEX index_name ON some_table (public.insensitive_query(NAME));

Re:The state of full text search in PostgreSQL 12 by cies on Monday February 03, 2020 @03:33PM (#22228678)
> If you use a function like this, [each query] it will need to be run for every entry
Re:The state of full text search in PostgreSQL 12 by rzwitserloot on Monday February 03, 2020 @04:12PM (#22229169)
No; if you create an index for the item: `insensitiveQuery(some_table.name)` and you then attempt to run: `SELECT * FROM some_table WHERE insensitiveQuery(name) LIKE insensitiveQuery('Étranger');`, the engine would run the `insensitiveQuery` function on the string `'Étranger'`, and then look up that result in the index directly, which is O(nlogn) or faster. If you have a million tables, that'd run the function only once (on `Étranger`) and do about 20 lookups in the index before finding you the qualifying row(s).
Re:The state of full text search in PostgreSQL 12 by cies on Wednesday February 05, 2020 @03:06AM (#22244879)
Cheer. I did not get the index-on-a-derived-value bit.
Re:The state of full text search in PostgreSQL 12 by jrumbut on Monday February 03, 2020 @09:12PM (#22231690)
This is a lot more graceful than some of the other workarounds I've seen/tried before.

Thank you and I feel a little sheepish I didn't do it first!