sqlpostgresqlcollationcollateunaccent

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.