# Issue with postgres gin indexes

**URL:** <https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842>\
**Category:** Using the ORM\
**Created:** [March 24, 2025, 2:16pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842 "2025-03-24T14:16:06Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![kingbuzzman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kingbuzzman/32/27556_2.png) [@kingbuzzman](https://forum.djangoproject.com/u/kingbuzzman)\
**Post date:** [March 24, 2025, 2:16pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/1 "2025-03-24T14:16:06Z")

</div>

I’m using Django 4.2.latest, and i’m running into an issue with this model:

```auto
from django.contrib.postgres.indexes import GinIndex, OpClass

class Model(models.Model):
    ...
    class Meta:
        indexes = [
            GinIndex(OpClass(Lower('first_name'), 'gin_trgm_ops'), name='user_first_name_gin_unac_idx'),
            GinIndex(OpClass(Lower('last_name'), 'gin_trgm_ops'), name='user_last_name_gin_unac_idx'),
        ]

```

```auto
$ ./manage.py migrate
...
Traceback (most recent call last):
  File "/app/./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/ __init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python3.10/site-packages/django/core/management/ __init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 412, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 458, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/base.py", line 106, in wrapper
    res = handle_func(*args, **kwargs)
  File "/usr/local/lib/python3.10/site-packages/django/core/management/commands/migrate.py", line 356, in handle
    post_migrate_state = executor.migrate(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/executor.py", line 252, in apply_migration
    state = migration.apply(state, schema_editor)
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/usr/local/lib/python3.10/site-packages/django/db/migrations/operations/models.py", line 894, in database_forwards
    schema_editor.add_index(model, self.index)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 325, in add_index
    self.execute(
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/base/schema.py", line 201, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 102, in execute
    return super().execute(sql, params)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/usr/local/lib/python3.10/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/usr/local/lib/python3.10/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/usr/local/lib/python3.10/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 "gin_trgm_ops"
LINE 1: ...user" USING gin ((LOWER("first_name") gin_trgm_o...
                                                             ^

```

* * *

From what I can piece together the sql generated should be:

`CREATE INDEX "user_first_name_gin_unac_idx" ON "model" USING gin (LOWER("first_name") gin_trgm_ops)`

and not what it’s currently doing:  
`CREATE INDEX "user_first_name_gin_unac_idx" ON "model" USING gin ((LOWER("first_name") gin_trgm_ops))`

* * *

Is this an issue with my code? Or is this a bug?

---

<div class="post-metadata">

**Author:** ![kingbuzzman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kingbuzzman/32/27556_2.png) [@kingbuzzman](https://forum.djangoproject.com/u/kingbuzzman)\
**Post date:** [March 24, 2025, 2:18pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/2 "2025-03-24T14:18:15Z")

</div>

While I wait, i have a hack/workaround to get me over the issue:

```auto
from django.contrib.postgres.indexes import GinIndex as GinIndexBase

class GinIndex(GinIndexBase):
    def create_sql(self, model, schema_editor, using="", **kwargs):
        sql = 'CREATE INDEX %(name)s ON %(table)s%(using)s %(columns)s%(include)s%(extra)s%(condition)s'
        kwargs['sql'] = sql
        return super().create_sql(model, schema_editor, using=using, **kwargs)

```

The original implementation has an extra set of parentheses around `%(columns)s`

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 24, 2025, 2:40pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/3 "2025-03-24T14:40:42Z")

</div>

Not sure which version of Postgres you are using but I can’t reproduce with 4.2 and `main` against Postgres 14 (oldest supported Postgres version) with the following test

```diff
diff --git a/tests/postgres_tests/test_indexes.py b/tests/postgres_tests/test_indexes.py
index d063ac64a2..695c33c9ef 100644
--- a/tests/postgres_tests/test_indexes.py
+++ b/tests/postgres_tests/test_indexes.py
@@ -365,6 +365,22 @@ def test_trigram_op_class_gin_index(self):
             editor.remove_index(Scene, index)
         self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table))

+ def test_trigram_op_class_gin_index_expr(self):
+ index_name = "trigram_op_class_gin_lower"
+ index = GinIndex(OpClass(Lower("scene"), name="gin_trgm_ops"), name=index_name)
+ with connection.schema_editor() as editor:
+ editor.add_index(Scene, index)
+ with editor.connection.cursor() as cursor:
+ cursor.execute(self.get_opclass_query, [index_name])
+ self.assertCountEqual(cursor.fetchall(), [("gin_trgm_ops", index_name)])
+ constraints = self.get_constraints(Scene._meta.db_table)
+ self.assertIn(index_name, constraints)
+ self.assertIn(constraints[index_name]["type"], GinIndex.suffix)
+ with connection.schema_editor() as editor:
+ editor.remove_index(Scene, index)
+ self.assertNotIn(index_name, self.get_constraints(Scene._meta.db_table))
+
     def test_cast_search_vector_gin_index(self):
         index_name = "cast_search_vector_gin"
         index = GinIndex(Cast("field", SearchVectorField()), name=index_name)

```

The generated SQL also differs from what you are experiencing

```sql
CREATE INDEX "trigram_op_class_gin_lower"
ON "postgres_tests_scene" USING gin ((LOWER("scene")) gin_trgm_ops)

```

---

<div class="post-metadata">

**Author:** ![kingbuzzman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kingbuzzman/32/27556_2.png) [@kingbuzzman](https://forum.djangoproject.com/u/kingbuzzman)\
**Post date:** [March 24, 2025, 2:48pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/4 "2025-03-24T14:48:27Z")

</div>

I’m using Postgres 15, i just swapped over to 14, and i get the same issue.

```auto
django-1 | return self.cursor.execute(sql)
django-1 | django.db.utils.ProgrammingError: syntax error at or near "gin_trgm_ops"
django-1 | LINE 1: ...ON "model" USING gin ((UPPER("first_name") gin_trgm_o...

```

* * *

Unrelated, i swapped `LOWER` → `UPPER` so it would match the `__icontains` logic and match my index.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 24, 2025, 3:00pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/5 "2025-03-24T15:00:07Z")

</div>

Interesting, it seems that the SQL generated when using `add_index` during tests and when calling `migrate` isn’t the same as I can also get a crash against 4.2 by going the full route of defining models and associated migrations (haven’t been able to test against `main`).

If you can reproduce against `main` I would definitely file a bug for it.

---

<div class="post-metadata">

**Author:** ![kingbuzzman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kingbuzzman/32/27556_2.png) [@kingbuzzman](https://forum.djangoproject.com/u/kingbuzzman)\
**Post date:** [March 24, 2025, 4:26pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/7 "2025-03-24T16:26:15Z")

</div>

Right because 4.2 is not in active support, only security support.. and this is not security…

> **[Django](https://endoflife.date/django)**
>
> Check end-of-life, release policy and support schedule for Django.

I have a long way to get to 5.2/main.

Thanks

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [March 25, 2025, 3:10am UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/8 "2025-03-25T03:10:30Z")

</div>

I figured it out. You must make sure that `django.contrib.postgres` is part of your `INSTALLED_APPS` which will work on Django 4.2 as well.

[It is documented](https://docs.djangoproject.com/en/4.2/ref/contrib/postgres/indexes/#opclass-expressions)

> To use it, you need to add `'django.contrib.postgres'` in your [`INSTALLED_APPS`](https://docs.djangoproject.com/en/4.2/ref/settings/#std-setting-INSTALLED_APPS).

But it’s quite easy to miss.

Doing so ensures that [this code gets registered](https://github.com/django/django/commit/83fcfc9ec8610540948815e127101f1206562ead#diff-9512492a6741109f13bbde06b4386c2d76759fcc162f387cafaa2529d9d59da2R12-R69).

I’d appreciate if you could chime in on [this ticket about the exact same problem](https://code.djangoproject.com/ticket/32770) to support the idea of failing more loudly when such a mis-configuration is detected. There’s [a clear solution on how to implement it](https://code.djangoproject.com/ticket/32770#comment:5) but having someone else testimony could be valuable.

---

<div class="post-metadata">

**Author:** ![kingbuzzman](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kingbuzzman/32/27556_2.png) [@kingbuzzman](https://forum.djangoproject.com/u/kingbuzzman)\
**Post date:** [March 25, 2025, 9:20am UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/9 "2025-03-25T09:20:00Z")

</div>

Confirmed, I was missing `'django.contrib.postgres'` in my `INSTALLED_APPS`. Thank you so much for your help!

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [July 12, 2025, 8:39pm UTC](https://forum.djangoproject.com/t/issue-with-postgres-gin-indexes/39842/10 "2025-07-12T20:39:22Z")

</div>

FWIW thanks to @cliff688 [this will be fixed in Django 6.0](https://github.com/django/django/commit/74b31cd26b9ad4ad85f131850a734f02aae988bb).