I’m using Django and DRF searchFilter, which allows me to add fields to the view’s ‘search_fields’ to allow searching in them.
Django version is 3.2 (will probably upgrade to 4.2) and DRF version 3.12.2.
Lately I’ve added a new field of type CharField
with a db_collation of case_insensitive
. I need that field to be case-insensitive and I understood that Postgres will stop supporting CI
field and the recommendation is using db_collation. It did work until I wanted to support searching in that field. After adding that new field to the ‘search_fields’ of the view and trying searching, I got this error message –
File ".../lib/python3.7/site-packages/django/db/backends/utils.py", line 84, in _execute
return self.cursor.execute(sql, params)
django.db.utils.NotSupportedError: nondeterministic collations are not supported for LIKE
Is there a pre-prepared solution for that?
I couldn’t find any and I find it weird that Postgres enforce using db_collation but doesn’t support LIKE in it.
This is the field definition in the model –
custom_field = models.CharField(db_collation="case_insensitive", db_index=True, max_length=100, null=True)
This is the collation definition –
[
CreateCollation(
"case_insensitive",
provider="icu",
locale="und-u-ks-level2-kn-true",
deterministic=False,
),
]
2
Answers
Based on the discussion here, This has been fixed in Django 4.2. A workaround for Django 3 (3.2.x, to be exact) is to subclass the Postgres SchemaEditor to override its
_create_like_index_sql
method and use the adjusted backend in the DATABASES entries.If a
varchar_pattern_ops
index exists on the Postgres DB, altering the field may not be sufficent and it may need to be recreated.Hope this helps!
You mentioned that the issue is with queries containing
LIKE
. If so, that may not be supported by PostgreSQL: