Icontains lookup when using nondeterministic collations in postgres fails

You can, yes, but not while using the varchar_pattern_ops operator class, which means the index can’t be used for any kind of partial match query such as LIKE/ILIKE.

Which as far as I can tell, Postgres desn’t support anyway:

From PostgreSQL: Documentation: 16: 9.7. Pattern Matching

The pattern matching operators of all three kinds do not support nondeterministic collations. If required, apply a different collation to the expression to work around this limitation.

As mentioned here, and as Adam says, you can get around this by using Collate() in the query. However, your index won’t work for this either because it uses a different collation to your query. Now… can you craft an index that uses Collate() and varchar_pattern_ops, that will work? I don’t think so, I think Postgres would still tell you that the opclass isn’t supported, but might be worth a try.

So I think my initial suggestion stands: if you need to do partial matching on a case-insensitive field, it’s probably easier to have the field be a regular CharField, using an index with Upper() and making sure you always use icontains, perhaps by overriding the manager.

1 Like