Django Migration with OpClass and varchar_pattern_ops creates an invalid migration SQL

[Andra Antariksa]

I have this model

class Location(models.Model):
    name = models.CharField(max_length=255)
    phone = models.CharField(max_length=255)
    latitude = models.FloatField()
    longitude = models.FloatField()
    address = models.TextField()
    address_notes = models.TextField(blank=True, default='')
    is_active = models.BooleanField(default=True)

    class Meta:
        indexes = [
            models.Index(
                OpClass(Upper('name'), name='varchar_pattern_ops'), 'phone',
                name='uppercase_name_index'
            ),
        ]

    def __str__(self) -> str:
        return self.name

And I create a migration using Django makemigration which produce the following script

# Generated by Django 4.2.7 on 2024-01-02 09:12

import django.contrib.postgres.indexes
from django.db import migrations, models
import django.db.models.functions.text


class Migration(migrations.Migration):
    dependencies = [
        ("locations", "0004_providerlocationcode_sub_district"),
    ]

    operations = [
        migrations.AddIndex(
            model_name="location",
            index=models.Index(
                django.contrib.postgres.indexes.OpClass(
                    django.db.models.functions.text.Upper("name"),
                    name="varchar_pattern_ops",
                ),
                name="uppercase_name_index",
            ),
        ),
    ]

However I encounter an error when running the migration using ./manage.py migrate locations 0005

py manage.py migrate locations
System check identified some issues:

WARNINGS:
?: (urls.W005) URL namespace 'admin' isn't unique. You may not be able to reverse all URLs in this namespace
Operations to perform:
  Apply all migrations: locations
Running migrations:
  Applying locations.0005_location_uppercase_name_index...Traceback (most recent call last):
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
psycopg2.errors.SyntaxError: syntax error at or near "varchar_pattern_ops"
LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa...
                                                             ^


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

Traceback (most recent call last):
  File "/home/andra/Projects/fleet/manage.py", line 21, in <module>
    main()
  File "/home/andra/Projects/fleet/manage.py", line 17, in main
    execute_from_command_line(sys.argv)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/andra/Projects/fleet/venv/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/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/migrations/operations/models.py", line 894, in database_forwards
    schema_editor.add_index(model, self.index)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 325, in add_index
    self.execute(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/sentry_sdk/integrations/django/__init__.py", line 641, in execute
    return real_execute(self, sql, params)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/home/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/home/andra/Projects/fleet/venv/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/andra/Projects/fleet/venv/lib/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
django.db.utils.ProgrammingError: syntax error at or near "varchar_pattern_ops"
LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa...

I went investigate the generated SQL using ./manage.py sqlmigrate locations 0005. Below are the generated SQL

--
-- Create index uppercase_name_index on OpClass(Upper(F(name)), name=varchar_pattern_ops) on model location
--
CREATE INDEX "uppercase_name_index" ON "locations_location" ((UPPER("name") varchar_pattern_ops));
COMMIT;

I ran the generated SQL in my local postgres (psql (PostgreSQL) 14.9 (Ubuntu 14.9-0ubuntu0.22.04.1)), and it indeed error

psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1))
Type "help" for help.

postgres=# CREATE INDEX "uppercase_name_index" ON "locations_location" ((UPPER("name") varchar_pattern_ops));
ERROR:  syntax error at or near "varchar_pattern_ops"
LINE 1: ...me_index" ON "locations_location" ((UPPER("name") varchar_pa...                                                             ^

Although when I removed the outer parentheses of the statement of (UPPER("name") varchar_pattern_ops) it ran successfully

harvest_st_fleet=# CREATE INDEX "uppercase_name_index" ON "locations_location" (UPPER("name") varchar_pattern_ops);
CREATE INDEX