`icontains` lookup doesn't work with case insensitive collations

[Craig de Stigter]

As recommended by django's warning messages when upgrading to 4.x we replaced our use of the CITEXT db type with a case insensitive collation (specifically und-u-ks-level2)

We have discovered that this prevents us from using search in the Django admin. The error we receive is

NotSupportedError: nondeterministic collations are not supported for LIKE 

This makes sense since the collation cannot supprot case-sensitive comparison. However, django admin's search box is supposed to be case-insensitive. It explicitly uses icontains.

It turns out that icontains is implemented using UPPER(fieldname) LIKE UPPER(pattern). Some of this logic is ​here (although I'm not sure where the second UPPER gets added)

How to fix it?

1. It seems that icontains (and iexact etc) comparisons should use ILIKE, which would work fine in this situation. I have looked through git and ticket history and can't find any discussion of why it's implemented using LIKE instead.

2. Alternatively, using x LIKE y COLLATE "default" seems to avoid the issue in this case, although someone who knows more about collations should probably weigh in on what other side-effects that might have...

[Simon Charette]

​Relevant article on the subject.

It appears that Postgres 18+ does support LIKE against nondeterministic collations so that should be taken into consideration when taking a decision here.

It seems that icontains (and iexact etc) comparisons should use ILIKE, which would work fine in this situation

Are you sure of that? It appears that ILIKE is explicitly pointed out as not being implemented for nondeterministic collation as ​it's unclear whether or not it makes sense.

I have looked through git and ticket history and can't find any discussion of why it's implemented using LIKE instead.

Some context for you

• ticket:3575#comment:5
• ticket:32485

Alternatively, using x LIKE y COLLATE "default" seems to avoid the issue in this case, although someone who knows more about collations should probably weigh in on what other side-effects that might have...

Not an expert but I think this might cause more harm than good as it basically ignore the specified collation on the column?

For anyone running into this problem you can register field instance lookups to work around the problem

from django.db.models.lookups import Contains, IContains

class Foo(models.Model):
    bar = models.TextField(db_collation="und-u-ks-level2")


# See https://docs.djangoproject.com/en/5.1/howto/custom-lookups/#a-lookup-example
class NonDetermisticContains(Contains):
    def as_postgresql(self, compiler, connection):
        lhs, lhs_params = self.process_lhs(compiler, connection)
        rhs, rhs_params = self.process_rhs(compiler, connection)
        params = lhs_params + rhs_params
        return f'{lhs} COLLATE "default" LIKE {rhs}', params

Foo._meta.get_field("bar").register_lookup(NonDetermisticContains)

Here's ​the fiddle I used to review this ticket.

[Natalia Bidart]

Thank you, Craig, for the ticket report, and Simon for the additional context on PostgreSQL 18's improvements.

This issue has been previously discussed ​in this forum post, covering both LIKE lookups and index creation challenges. Additionally, ​Adam Johnson's post provides a step-by-step guide to addressing these issues.

Given that PostgreSQL 18 is expected in September 2025 and Django 6.0's feature freeze is set for September 17, 2025, support for PostgreSQL 18 will likely land in Django 6.1. Therefore, I'm inclined to closing this as wontfix for now. Any new findings can be shared in the referenced forum post, and if a clear implementation path emerges, I'd be happy to reopen this ticket.