Hacker News new | past | comments | ask | show | jobs | submit login

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




Registration is open for Startup School 2019. Classes start July 22nd.

Guidelines | FAQ | Support | API | Security | Lists | Bookmarklet | Legal | Apply to YC | Contact

Search: