id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
36374	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"")
"	Bug	closed	Database layer (models, ORM)	5.2	Normal	duplicate			Unreviewed	0	0	0	0	0	0