How to migrate from Django’s PostgreSQL CI Fields to use a case-insensitive collation

1. Add a new collation

As the release notes say, you need a “case-insensitive non-deterministic collation”. Let’s deconstruct that:

• A collation is a specification of how to compare and sort strings.
• Case-insensitive: upper and lower case letters compare equal, and sort together.
• Non-deterministic: a PostgreSQL term meaning that the collation needs to be used for sorting - PostgreSQL cannot rely on sorting by byte values.

Okay, so how do you create such a collation? Here’s an example from the PostgreSQL documentation on non-deterministic collations:

CREATE COLLATION case_insensitive (
    provider = icu, locale = 'und-u-ks-level2', deterministic = false
);

This might be the SQL you want, but let’s unpack it before looking at how to run it in Django migrations.

• CREATE COLLATION - creates a new collation within the current schema.

• case_insensitive - our chosen name for the collation.

• provider = icu - tells PostgreSQL to use the ICU library (International Components for Unicode) for this collation. You will need a version of PostgreSQL that was compiled with ICU, as per the managing collation docs.

  It’s normally available in OS packages, Docker images, and managed providers, etc. If you’re cautious, you can check with:

  example=# SELECT EXISTS(SELECT * FROM pg_collation WHERE collname LIKE '%-x-icu');
   exists
  --------
   t
  (1 row)
  

• und-u-ks-level2 is a language tag with extensions. It may look like gobbledygook at first, but we can systematically deconstruct it using the Unicode standards:

  • und - undetermined language, which activates the Unicode “root collation”. This collation sorts symbols first, then alphabetically per script.

    You may wish to use language-specific sorting, in which case swap this for a language code, e.g. de for Germany.

  • -u- - this specifies that what follows are extra “Extension U” Unicode attributes. These are all drawn from this table.

  • ks-level2 - the ks attribute defines the collation strength, here set to “level 2”. Strength level 2 doesn’t include case in comparisons, only letters and accents. The best explanation of the strength levels I found was in the ICU documentation.

    You may wish to use level 1 instead, for accent-insensitive comparisons.

  Another neat attribute you might want to add (after another -): kn-true - numeric ordering. This will sort e.g. “a-20” before “a-100”.

• deterministic = false tells PostgreSQL the collation is non-deterministic, as above.

Okay so that’s the collation creation SQL analyzed. Time to put this statement into a migration. You could use RunSQL, but it’s more Djangoey and reversible to use the CreateCollation operation (added in Django 3.2).

Create an empty migration in your “main” or “core” Django app:

$ ./manage.py makemigrations example --empty --name case_insensitive_collation
Migrations for 'example':
  example/migrations/0005_case_insensitive_collation.py

Then, edit that migration file to include a CreateCollation operation:

from django.contrib.postgres.operations import CreateCollation
from django.db import migrations


class Migration(migrations.Migration):
    dependencies = [
        ("example", "0004_something_or_other"),
    ]

    operations = [
        CreateCollation(
            "case_insensitive",
            provider="icu",
            locale="und-u-ks-level2",
            deterministic=False,
        ),
    ]

Using the sqlmigrate command, you can see the SQL that Django will run for the migration, forwards and backwards:

$ python manage.py sqlmigrate example 0005
BEGIN;
--
-- Create collation case_insensitive
--
CREATE COLLATION "case_insensitive" (locale="und-u-ks-level2", provider="icu", deterministic=false);
COMMIT;

$ python manage.py sqlmigrate example 0005 --backwards
BEGIN;
--
-- Create collation case_insensitive
--
DROP COLLATION "case_insensitive";
COMMIT;

Alright, step one done 😅.

2. Alter fields

Replace all CI*Fields in your models with their vanilla variants, specifying db_collation with your collation name. For example, to update this User class:

 class User(AbstractBaseUser):
-    email = CIEmailField(_("email address"), unique=True)
+    email = models.EmailField(_("email address"), db_collation="case_insensitive", unique=True)
     ...

Then, generate migrations:

$ ./manage.py makemigrations
Migrations for 'users':
  users/migrations/0010_alter_user_email.py
    - Alter field email on user

(If you have models in your “main” / “core” app, you may wish to combine with the above CreateCollation migration.)

Check the new migration with sqlmigrate as well:

$ ./manage.py sqlmigrate users 0010
BEGIN;
--
-- Alter field email on user
--
DROP INDEX IF EXISTS "users_user_email_243f6e77_like";
ALTER TABLE "users_user" ALTER COLUMN "email" TYPE varchar(254) COLLATE "case_insensitive";
COMMIT;

$ ./manage.py sqlmigrate users 0010 --backwards
BEGIN;
--
-- Alter field email on user
--
DROP INDEX IF EXISTS "users_user_email_243f6e77_like";
ALTER TABLE "users_user" ALTER COLUMN "email" TYPE citext;
COMMIT;

All looks as expected, with ALTER COLUMN statements to change the column’s data type.

(The DROP INDEX IF EXISTS statement ensures no “like” index exists. This is an index that Django adds for CharField / TextField classes, to speed up SQL LIKE matches. Setting db_collation makes Django remove any such index, since non-deterministic collations do not support LIKE, or such an index. Since the column previously used the citext type, no such index will exist anyway.)

One caveat: if you’re moving from CICharField or CIEmailField to their vanilla variants, your data might be too long. The CI*Fields all use the citext type, which is unlimited in size, whilst CharField and EmailField use varchar. If your data doesn’t fit, PostgreSQL will error at migration time like:

silvr@a67bfe06cb5c:/opt/silvr$ ./manage.py migrate users 0010
Operations to perform:
  Target specific migration: 0010_alter_user_email, from users
Running migrations:
  Applying users.0010_alter_user_email...Traceback (most recent call last):
  ...
  File "/.../site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.DataError: value too long for type character varying(254)

You can check ahead of time by querying your database:

In [2]: from django.db.models.functions import Length

In [3]: from django.db.models import Max

In [4]: User.objects.aggregate(m=Max(Length("email")))
Out[4]: {'m': 312}

You can mitigate this by specifying a larger max_length, or migrating to TextField instead.

3. Test-ify

With more involved model definitions, it’s best to test to ensure all the pieces are working correctly. Here are a couple tests checking the above User.email field is case-insensitive and unique:

from django.db import IntegrityError
from django.test import TestCase

from users.models import User


class UserTests(TestCase):
    def test_email_case_insensitive_search(self):
        user = User.objects.create(email="Hacker@example.com")
        user2 = User.objects.get(email="hacker@example.com")
        assert user == user2

    def test_email_case_insensitive_unique(self):
        User.objects.create(email="Hacker@example.com")
        msg = 'duplicate key value violates unique constraint "users_user_email_key"'
        with self.assertRaisesMessage(IntegrityError, msg):
            User.objects.create(email="hacker@example.com")

Adapt as necessary.

4. Adjust queries

Some of Django’s lookups, like icontains, map to a SQL LIKE. PostgreSQL does not allow such queries with nondeterministic collations—you will see this error if such a query is attempted:

NotSupportedError: nondeterministic collations are not supported for LIKE

One solution is to use the Collate database function to create a differently-collated annotation for the field, and filter against that:

from django.db.models.functions import Collate
from users.models import User

User.objects.annotate(
    email_deterministic=Collate("email", "und-x-icu"),
).filter(email_deterministic__icontains="example")

und-x-icu is a general purpose, language-agnostic Unicode collation from ICU.

I encountered this within the admin, which uses icontains by default for search fields. So the solution was to add the annotation in get_queryset() and then declare it in search_fields:

...


class UserAdmin(BaseUserAdmin):
    search_fields = ("email_deterministic", ...)

    ...

    def get_queryset(self, request: HttpRequest) -> QuerySet[User]:
        return (
            super()
            .get_queryset(request)
            .annotate(
                email_deterministic=Collate("email", "und-x-icu"),
            )
        )

You may want to check for performance regressions, since such lookups probably need table scans. That said, it’s likely LIKE against citext columns also requires a table scan.

Alright, that is all it takes to migrate