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

by

3👍

✅

It isn’t related to Django itself, PostgreSQL’s lc_collate configuration determines this. I’d suggest you to review its value:

SHOW lc_collate;

The right thing to do is fix this configuration. Don’t forget to take a look on related settings too (lc_ctype, etc.).

But if you cannot create another database with the right setting, try to explicit collate on ORDER like the following test case:

CREATE TEMPORARY TABLE table1 (column1 TEXT); 

INSERT INTO table1 VALUES('Barn'),
('beef'),
('bémol'),
('Bœuf'),
('boulette'),
('Bubble');

SELECT * FROM table1 ORDER BY column1 COLLATE "en_US"; --Gives the expected order
SELECT * FROM table1 ORDER BY column1 COLLATE "C"; --Gives "wrong" order  (in your case)

It’s important to remember that PostgreSQL relies on operating system locales. This test case was executed on CentOS 7. More info here and here.

👤Michel Milezzi

0👍

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, 😉

👤Mateus Padua

Leave a comment