[SOLVED] postgresql unaccent and case insensitive

postgresql unaccent and case insensitive

I have this query :

select * from entrees 
where account_id = 1 
and categorie_id = 2 
and recurrent = false 
and montant < 0 
and ABS(montant) >= 50000 
and date_de_valeur >= '2025-05-01' 
and date_de_valeur < '2025-06-01' 
and LOWER(intitule) = LOWER(unaccent('Révolut'));

This returns the rows that have Intitule 'Revolut', but not 'révolut'.

What must I change so that the query returns all rows with intitul 'Revolut', 'revolut', 'Révolut', 'révolut'?

Solution

Here is one SQL query that should work:

select * from entrees 
where account_id = 1 
and categorie_id = 2 
and recurrent = false 
and montant < 0 
and ABS(montant) >= 50000 
and date_de_valeur >= '2025-05-01' 
and date_de_valeur < '2025-06-01' 
and LOWER(unaccent(intitule)) = LOWER(unaccent('Révolut'));

I think you forgot to add unaccent function to the intitule columns.