In Django with Postgresql 9.6 how to sort case and accent insensitive?

user_146481 0
Viewed 748

What I would like is the equivalent of using utf8_unicode_ci in MySQL. So if I have those strings (default sort order with Postgresql):

  • Barn
  • Bubble
  • Bœuf
  • beef
  • boulette
  • bémol

I wish they would be sorted like this (as with utf8_unicode_ci in MySQL):

  • Barn
  • beef
  • bémol
  • Bœuf
  • boulette
  • Bubble

This kind of sort is case insensitive, accent insensitive and ligatures are converted to multiple characters.

I know about unaccent and lower in Postgresql but I have no idea how to use them from Django.

Possible solutions with Django/Postgresql:

  • Add new column only for sorting with data normalized (lower, unaccent).
  • Add an index (like in this answer), but I'm not sure how it will work with Django?

I don't think Full Text Search or Trigram could help me here because I'm not necessarily doing searches base on text but I need to get the good sort order.

Ideally queries should be fast so using another indexed column looks like a good avenue. But I wish to find a solution that I don't need to implement for every exisiting text column in my DB, that is easy to maintain, etc. Is there a best practice to do that?

postgresql sorting django
2 Answers

I did this way:

But you need enable in your postgresql the module 'unaccent' before this way: CREATE EXTENSION unaccent;

def get_value_ci(field):
    return Func(field, function='LOWER', template='UNACCENT(%(function)s(%(expressions)s))')

YoutModel.objects.order_by(get_value_ci('nome_your_field'))

and works, ;)

user_13594750
Related