Django psycopg2.errors.InvalidSchemaName: no schema has been selected to create in

Question

I am trying to configure PostgreSQL in my Django project with a custom schema. My settings are:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': os.environ['DB_DATABASE'],
        'USER': os.environ['DB_USERNAME'],
        'PASSWORD': os.environ['DB_PASSWORD'],
        'HOST': os.environ.get('DB_HOST', 'localhost'),
        'PORT': os.environ.get('DB_PORT', '5432'),
        'OPTIONS': {
            'options': '-c search_path=my_custom_schema',
        },
    }
}

I want django system tables to be created in the schema my_custom_schema as well. When I do first migration I get the error.

$ python manage.py migrate
...
psycopg2.errors.InvalidSchemaName: no schema has been selected to create in
LINE 1: CREATE TABLE "django_migrations" ("id" bigserial NOT NULL PR...
...

How do I resolve it?

I have also tried such option:

'OPTIONS': {
    'options': '-c search_path=my_custom_schema,public',
},

The migration went well but the tables were created in public schema instead of my_custom_schema.

Python version: 3.9.7

Django version: 3.2.8

Psycopg2 version: psycopg2-binary==2.9.1

PostgreSQL version: 12.8

may be this can help you https://stackoverflow.com/questions/41207259/no-schema-has-been-selected-to-create-in-error — sunil ghimire, Oct 25 '21 at 10:35

score 6 · Answer 1 · answered Oct 25 '21 at 11:14

I resolved the issue by my own. The reason was unexpected, because the error is not that informative. I checked the permission to create tables in the schema my_custom_schema for the user and found out the it's not permitted. So after I granted the permission everything worked well.

GRANT ALL ON SCHEMA my_custom_schema TO my_user;

Django psycopg2.errors.InvalidSchemaName: no schema has been selected to create in

1 Answers1