skip to Main Content

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


  1. 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!

    Login or Signup to reply.
  2. You mentioned that the issue is with queries containing LIKE. If so, that may not be supported by PostgreSQL:

    Also, certain operations are not possible with nondeterministic collations, such as pattern matching operations. Therefore, they should be used only in cases where they are specifically wanted.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search