Postgresql accent insensitive for non-accent and sensitive for accent word

Question

In Postgresql how to achieve separate search result with and without accent using like/ilike, something like this:

• The record names are: cafe, café, cafeteria

• If I use select name from cafe-table where name like '%cafe%'; then the result will be cafe, café, cafeteria

• But If I select with %café% (with the accent) I just want the result is café

I've already tried unaccent but it makes the result the same for 2 queries.

Answer 1

You can use the unaccent module:

Install:

CREATE EXTENSION unaccent;

Then your query could look like this:

SELECT name 
FROM cafe-table 
WHERE unaccent(name) like '%cafe%'; -- use unaccent here