

If you want to do case-insensitive for all languages you can do this:

1. first install the following (be sure to replace [your schema]:

CREATE EXTENSION pg_trgm with schema extension;

CREATE EXTENSION unaccent with schema extension;

CREATE OR REPLACE FUNCTION insensitive_query(text) RETURNS text AS $func$ SELECT lower([your schema].unaccent('[your schema].unaccent', $1)) $func$ LANGUAGE sql IMMUTABLE;

2. then in your query you can use:

where insensitive_query(my_table.name) LIKE insensitive_query('Bob')

That will not work for all languages. Look at https://www.w3.org/International/wiki/Case_folding for an explanation of why this problem is nontrivial. The lower function is sufficient: it handles case-folding properly, using the configured locale. Explicitly stripping accents can actually be the wrong choice depending on the locale

