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