The test database of django return error collation case_insensitive for encoding "UTF8"

Question

I am using django 4.2 and the CICharField was depreciated. And we need to use db_collation in CharField. I create a migration bellow to create collation case_insensitive

# Generated by Django 4.2.8 on 2023-12-08 17:11

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


def create_collaction(apps, schema_editor):
    try:
        CreateCollation(
            'case_insensitive',
            provider='icu',
            locale='und-u-ks-level2',
            deterministic=False
        )
    except Exception:
        pass


class Migration(migrations.Migration):
    dependencies = [
        ('module', ''),
    ]

    operations = [
        migrations.RunPython(create_collaction),
    ]

In the normal database, all things run normal, but when I run python manage.py test, I receive the error:

Creating test database for alias 'default'...
Got an error creating the test database: database "test_database" already exists

Type 'yes' if you would like to try deleting the test database 'test_database', or 'no' to cancel: yes
Destroying old test database for alias 'default'...
Traceback (most recent call last):
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.UndefinedObject: collation "case_insensitive" for encoding "UTF8" does not exist


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/epitacio/workspace/smart-one/manage.py", line 41, in <module>
    main()
  File "/home/epitacio/workspace/smart-one/manage.py", line 37, in main
    execute_from_command_line(sys.argv)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/commands/test.py", line 24, in run_from_argv
    super().run_from_argv(argv)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/commands/test.py", line 68, in handle
    failures = test_runner.run_tests(test_labels)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/test/runner.py", line 1054, in run_tests
    old_config = self.setup_databases(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/test/runner.py", line 950, in setup_databases
    return _setup_databases(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/test/utils.py", line 221, in setup_databases
    connection.creation.create_test_db(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/base/creation.py", line 78, in create_test_db
    call_command(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/__init__.py", line 194, in call_command
    return command.execute(*args, **defaults)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/migrations/operations/fields.py", line 235, in database_forwards
    schema_editor.alter_field(from_model, from_field, to_field)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 831, in alter_field
    self._alter_field(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 288, in _alter_field
    super()._alter_field(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 1056, in _alter_field
    self.execute(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 48, in execute
    return super().execute(sql, None)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/epitacio/.cache/pypoetry/virtualenvs/project-jss6nqd0-py3.9/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: collation "case_insensitive" for encoding "UTF8" does not exist

Bellow is the model with CharField with db_collation

class Model(models.Model):
    name = models.CharField(
        "Name",
        max_length=255,
        unique=True,
        blank=False,
        null=False,
        db_collation="case_insensitive"
    )
    description = models.TextField("Description", blank=True, null=True)
    created_at = models.DateTimeField(
        "Created at", editable=False, auto_now_add=True, null=True
    )
    updated_at = models.DateTimeField("Updated at", auto_now=True, null=True)

    def __str__(self):
        return self.name

Answer 1

it seems like there might be an issue with the way you're creating the collation in your migration file, the CreateCollation operation is not being executed properly in your create_collaction function, instead of instantiating the CreateCollation class you should use schema_editor.execute() to execute the SQL statement so your code should looks like this:

from django.db import migrations


def create_collaction(apps, schema_editor):
    try:
       schema_editor.execute('CREATE COLLATION case_insensitive (provider = icu, locale = und-u-ks-level2, deterministic = false)')
    except Exception as e:
        print(f"Error creating collation: {e}")


class Migration(migrations.Migration):
    dependencies = [
        ('module', ''),
    ]

    operations = [
        migrations.RunPython(create_collaction),
    ]

make sure to adjust the SQL statement as needed to your specific collation requirements

I hope this helps you

Answer 2

The error is related to the collation to not be created yet when the migrations using it are run. So, it's an issue from the order in which Django runs the migrations. We must play with it to make it work. I

n my case I had to create the migration in which we create the collation in a Django app that is not involved in any field change. A "core" or "common" app that you may have makes sense, and that's what this article recommends.

Next, we must make sure that the migrations using this new collation are executed after it, so we should add it to their dependencies property. For example:

dependencies = [
    ("users", "0041_any_other_previous_dependency"),
    ("common", "0001_case_insensitive_collation"),  # We must add this manually
]

You can run tests or deploy your Django app anywhere and it should work correctly. I recommend that you add some tests that would check that those tests are indeed case insensitive.