postgres ExclusionConstraint with multiple expressions breaks `create_model`

[anthony sottile]

the long and the short of this is CREATE EXTENSION btree_gist; needs to be run at least once for this type of constraint to be possible -- but django doesn't do this automatically for create_model when utilizing the test database

in searching for related issues I found https://code.djangoproject.com/ticket/33982 but that doesn't seem directly tied to this problem

seems others have hit this as well without solution:

• ​https://stackoverflow.com/questions/23949024/using-postgresql-gin-or-gist-index-with-bigint-column
• ​https://stackoverflow.com/questions/42329415/error-getting-when-creating-gin-index-on-jsonb-column-postgresql9-5
• ​https://dba.stackexchange.com/questions/275946/postgres-create-index#comment541407_275946
• ​https://stackoverflow.com/questions/45833855/prevent-daterangefield-overlap-in-django-model#comment139392235_59912678

(I understand for migrations I need BtreeGistExtension() -- but that isn't relevant here as I do not want to run migrations for general tests)

_

"minimal" reproduction

starting from django-admin startproject mysite .

• add mysite to INSTALLED_APPS

• add this to mysite/settings.py (or whatever port / user / password for postgres):

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.postgresql",
        "USER": "postgres",
        "NAME": "django",
        "PASSWORD": "postgres",
        "HOST": "localhost",
        "PORT": 5432,
    },
}

• add this models.py file:

from django.db import models
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import DateTimeRangeField


class MyModel(models.Model):
    subscription_id = models.BigIntegerField()
    target_type = models.BigIntegerField()
    period = DateTimeRangeField()

    class Meta:
        app_label = "mysite"
        db_table = "my_model"
        constraints = [
            ExclusionConstraint(
                name="accounts_spend_allocations_unique_per_period",
                expressions=(
                    ("subscription_id", "="),
                    ("target_type", "="),
                    ("period", "&&"),
                ),
            )
        ]

• create tests/test.py:

from django.test import TestCase

class TestMyTest(TestCase):
    def test(self):
        pass

$ python manage.py test tests --noinput
Found 1 test(s).
Creating test database for alias 'default'...
Got an error creating the test database: database "test_django" already exists

Destroying old test database for alias 'default'...
Traceback (most recent call last):
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ~~~~~~~~~~~~~~~~~~~^^^^^
psycopg2.errors.UndefinedObject: data type bigint has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.


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

Traceback (most recent call last):
  File "/private/tmp/y/manage.py", line 22, in <module>
    main()
    ~~~~^^
  File "/private/tmp/y/manage.py", line 18, in main
    execute_from_command_line(sys.argv)
    ~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
    ~~~~~~~~~~~~~~~^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/commands/test.py", line 24, in run_from_argv
    super().run_from_argv(argv)
    ~~~~~~~~~~~~~~~~~~~~~^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/base.py", line 416, in run_from_argv
    self.execute(*args, **cmd_options)
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/commands/test.py", line 63, in handle
    failures = test_runner.run_tests(test_labels)
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/test/runner.py", line 1092, in run_tests
    old_config = self.setup_databases(
        aliases=databases,
        serialized_aliases=suite.serialized_aliases,
    )
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/test/runner.py", line 990, in setup_databases
    return _setup_databases(
        self.verbosity,
    ...<5 lines>...
        **kwargs,
    )
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/test/utils.py", line 204, in setup_databases
    connection.creation.create_test_db(
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        verbosity=verbosity,
        ^^^^^^^^^^^^^^^^^^^^
    ...<2 lines>...
        serialize=False,
        ^^^^^^^^^^^^^^^^
    )
    ^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/base/creation.py", line 78, in create_test_db
    call_command(
    ~~~~~~~~~~~~^
        "migrate",
        ^^^^^^^^^^
    ...<3 lines>...
        run_syncdb=True,
        ^^^^^^^^^^^^^^^^
    )
    ^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/__init__.py", line 194, in call_command
    return command.execute(*args, **defaults)
           ~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/base.py", line 460, in execute
    output = self.handle(*args, **options)
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/commands/migrate.py", line 318, in handle
    self.sync_apps(connection, executor.loader.unmigrated_apps)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/core/management/commands/migrate.py", line 480, in sync_apps
    editor.create_model(model)
    ~~~~~~~~~~~~~~~~~~~^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/base/schema.py", line 512, in create_model
    self.execute(sql, params or None)
    ~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/postgresql/schema.py", line 45, in execute
    return super().execute(sql, params)
           ~~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/base/schema.py", line 204, in execute
    cursor.execute(sql, params)
    ~~~~~~~~~~~~~~^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ~~~~~~~~~~~~~~~~~~~~~~~~~~~^
        sql, params, many=False, executor=self._execute
        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
    )
    ^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/private/tmp/y/venv/lib/python3.13/site-packages/django/db/backends/utils.py", line 103, in _execute
    return self.cursor.execute(sql)
           ~~~~~~~~~~~~~~~~~~~^^^^^
django.db.utils.ProgrammingError: data type bigint has no default operator class for access method "gist"
HINT:  You must specify an operator class for the index or define a default operator class for the data type.

(if I manually --reusedb and inject the CREATE EXTENSION command above via psql then it continues as normal -- but that's a workaround "at best")

[Simon Charette]

Duplicate of #35902, see ticket:35902#comment:1 for a workaround using ​a test database template.

[anthony sottile]

a template db doesn't really help -- *something* would need to set that up and django has all the context and knowledge to do that for me but... doesn't

[anthony sottile]

this seems more helpful (and is not as specific as it should be but solves at least my issue) but I think something like this could live in the postgres schema editor:

class MakeBtreeGistSchemaEditor(PostgresDatabaseSchemaEditor):
    """workaround for https://code.djangoproject.com/ticket/36374"""
    def create_model(self, model: type[Model]) -> None:
        if any(isinstance(c, ExclusionConstraint) for c in model._meta.constraints):
            self.execute('CREATE EXTENSION IF NOT EXISTS btree_gist;')
        super().create_model(model)