django.db.utils.ProgrammingError: relation already exists

Question

I'm trying to set up the tables for a new django project (that is, the tables do NOT already exist in the database); the django version is 1.7 and the db back end is PostgreSQL. The name of the project is crud. Results of migration attempt follow:

python manage.py makemigrations crud

Migrations for 'crud':
  0001_initial.py:
    - Create model AddressPoint
    - Create model CrudPermission
    - Create model CrudUser
    - Create model LDAPGroup
    - Create model LogEntry
    - Add field ldap_groups to cruduser
    - Alter unique_together for crudpermission (1 constraint(s))

python manage.py migrate crud

Operations to perform:
  Apply all migrations: crud
Running migrations:
  Applying crud.0001_initial...Traceback (most recent call last):
  File "manage.py", line 18, in <module>
    execute_from_command_line(sys.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 385, in execute_from_command_line
    utility.execute()
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 377, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **options.__dict__)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 338, in execute
    output = self.handle(*args, **options)
  File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 161, in handle
    executor.migrate(targets, plan, fake=options.get("fake", False))
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 68, in migrate
    self.apply_migration(migration, fake=fake)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 102, in apply_migration
    migration.apply(project_state, schema_editor)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/migration.py", line 108, in apply
    operation.database_forwards(self.app_label, schema_editor, project_state, new_state)
  File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/operations/models.py", line 36, in database_forwards
    schema_editor.create_model(model)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/schema.py", line 262, in create_model
    self.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/schema.py", line 103, in execute
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 82, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 66, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 66, in execute
    return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: relation "crud_crudpermission" already exists

Some highlights from the migration file:

dependencies = [
    ('auth', '0001_initial'),
    ('contenttypes', '0001_initial'),
]
    migrations.CreateModel(
        name='CrudPermission',
        fields=[
            ('id', models.AutoField(verbose_name='ID', serialize=False, auto_created=True, primary_key=True)),
            ('_created_by', models.CharField(default=b'', max_length=64, null=True, editable=False, blank=True)),
            ('_last_updated_by', models.CharField(default=b'', max_length=64, null=True, editable=False, blank=True)),
            ('_created', models.DateTimeField(null=True, editable=False, blank=True)),
            ('_last_updated', models.DateTimeField(null=True, editable=False, blank=True)),
            ('domain', models.CharField(max_length=32, choices=[(b'town', b'Town'), (b'boe', b'BOE'), (b'police', b'Police')])),
            ('ldap_group', models.CharField(max_length=128, verbose_name=b'LDAP group')),
            ('can_add', models.BooleanField(default=False, verbose_name=b'add')),
            ('can_change', models.BooleanField(default=False, verbose_name=b'change')),
            ('restrict_change_to_own', models.BooleanField(default=False)),
            ('can_delete', models.BooleanField(default=False, verbose_name=b'delete')),
            ('restrict_delete_to_own', models.BooleanField(default=False)),
            ('models', models.ManyToManyField(to='contenttypes.ContentType', null=True, blank=True)),
        ],
        options={
            'verbose_name': 'CRUD permission',
        },
        bases=(models.Model,),
    ),
    migrations.AlterUniqueTogether(
        name='crudpermission',
        unique_together=set([('ldap_group', 'can_add', 'can_change', 'can_delete', 'domain')]),
    )

,

The crud app is not meant to actually do anything, but I use it another app, so when I try migrate from that app, I trigger the above problem.

I've found other examples on the web of people with similar issues, but none of their cases seem to apply because

1. The problem affects an entire relation, not just one column
2. I am not using multiple inheritance.

Where should I look next to find the underlying problem?

Fear

Have you run syncdb? If so, that has already created the table in the DB so this migration is trying to recreate. To skip it, run python manage.py migrate --fake – Botts

syncdb is nothing more than a migrate along with a prompt to create superuser if there isn't one. If he did run it, migrations will not try to reapply the migration. – Betake

Answer 1

This works pretty fine

./manage.py migrate --fake default

https://docs.djangoproject.com/en/2.2/ref/django-admin/#cmdoption-migrate-fake

Chetnik

I always forget what is "default", so I want to mention that this is the name of DB profile in django settings - docs.djangoproject.com/en/1.9/ref/settings/#databases – Encipher

Please be careful when using the fake option. It merely silences the problem by making the migration stored in the db match the local folder but when making the next migration, things will go very wrong so I'd recommend not using "fake" unless you really understand what is going on. – Tennyson

@Tennyson - what do you mean by things will go very wrong? I have used this fake option a couple of times when I get the same programming error. Up until now I have had no problems. ATM I can't migrate because a table exits already, after I makemigrations in my app. Suddenly --fake won't fix this. – Spinoff

@Spinoff the fake option will make django assume that you've migrated already. If it happens that there is anything even a small thing not migrated, then it will not migrate it because you used the fake option. I'd only use the fake option if I know for a fact that the only change was the table that django is complaining about and I do a sanity check to ensure that the table actually exists with the same name. My point is that if you have 2 changes and one of them is the create table which already exists, if you fake it then the second change will not take effect. – Tennyson

Showmigrations will tell you where the database is in terms of migrations - and there are the files in the migrations folder with the same names. These should match yes?. You are saying that the DB can actually be in a different state than it thinks it is? Is this what fake can make possible? Not much to read about in the docs. link – Spinoff

So what should we do then @max, please give a genuine solution for this if you know.... Thanks – Lynnelynnea

@NikhilBhardwaj I'll post my answer below. – Tennyson

As @Tennyson said, this doesn't really solve the problem. I found this helpful #30546062 – Philana

I get CommandError: No installed app with label 'default'. – Send

CommandError: No installed app with label 'default'. – Ramble

Answer 2

Do python manage.py migrate --fake initally.

https://docs.djangoproject.com/en/3.2/ref/django-admin/#django-admin-migrate

Otoole

The link is broken – Philana

try this one instead: docs.djangoproject.com/en/3.1/ref/django-admin/… – Urbanus

Answer 3

I've faced similar issue when added couple new fields to existing model. I'm using Django 1.9, which introduced --run-syncdb option. Running manage.py migrate --run-syncdb fixed my tables.

Antechoir

This helped in my situation, where it was necessary to create a new table for my custom defined AUTH_USER_MODEL, so this option --run-syncdb achieved the desired result – Bradlybradman

running `python manage.py migrate --fake' then running 'manage.py migrate --run-syncdb' worked for me – Cragsman

The documentation says this about --run-syncdb: Allows creating tables for apps without migrations. While this isn’t recommended, the migrations framework is sometimes too slow on large projects with hundreds of models. So proceed with caution... – Ejector

Answer 4

Now (I'm using Django 1.9) you can make:

./manage.py migrate [--database DATABASE] --fake [app_label] [migration_name]

This way you're targeting the problem with more accuracy, and you can fake only the problematic migration on the specific database.

So, looking at the question, you could:

./manage.py migrate --database default --fake crud crud.0001_initial

Boating

You are missing the actual command for manage.py. It should be: ./manage.py migrate --database default --fake crud crud.0001_initial – Solidify

I had an issue with some git merge conflicts leading to one migration having to be reapplied with a different file name. And I got it fixed with just this python manage.py migrate --database default --fake <appname>. – Aviles

Answer 5

Been facing a similar issue, eventually deleted all .py files in migration folder (django 1.7 creates one automatically), worked perfectly after that.

Acclamation

any side effects of this? – Wendiwendie

Would cause problems while migrating in production. Try this or dumping the database only for the initial migration or in dev – Spermiogenesis

Answer 6

In my case, a migration file was deleted and a new one was auto-generated, which had a different name. Because of the name difference, Django tried to apply the new migration file, which was exactly same as the previously applied one, which was now removed. In both of them, a new model had to be created which resulted in django.db.utils.ProgrammingError: relation "app_space" already exists. I tried to reverse the migration, but the missing migration file prevented django from actually reversing it. Lesson learnt, migration files should be checked into git.

Here are some steps that helped me to get to the bottom of this. --fake solved it temporarily but the same issue happened in the next migration. I wouldn't recommend --fake unless you know for sure it is the right use case for it.

This answer was really key for me.

1. Check previous migrations python manage.py showmigrations

2. Check applied migrations in Django DB select * from django_migrations; (use psql to access postgres db console: psql -h 0.0.0.0 -U <your-db-user> then use target db \c <your-db-name>).

3. I saw an applied migration that was no longer in my migrations folder.

 20 | app             | 0001_initial                             | 2021-03-05 07:40:20.14602+00
 21 | app             | 0002_some_auto_name                      | 07:40:20.269775+00
 22 | app             | 0003_auto_20210318_2350 <---here         | 2021-03-18 23:50:09.064971+00

But in migrations folder I had 0003_auto_20210318_2355, the same file generated 5 minutes later. I renamed the migration file to the name above so that I could reverse it.

4. Reverse the migration by passing the migration to which you want to return.

python manage.py migrate <app-name> <latest-migration-to-which-to-return>

python manage.py migrate app 0002_some_auto_name

5. Do the right thing from here and check in the migrations to git. Then you can do makemigrations and migrate and have a more peaceful life.

Jeraldinejeralee

Answer 7

I was facing the similar issues, where i had changed column name. I was getting same error as mentioned in the stack-trace provided with he question.

Here's what I did.

I ran fake migrations first. Then i removed it's(migrations i wanted to run) entry from django_migrations table and ran migrations again(no fake this time).

Changes appeared as expected for me.

hope this is helpful.

Torin

This didn't make any sense to me but it worked. I had added null=True to a column (django==1.11). Can someone explain why it worked? – Greenburg

This worked for me in django==2.2 in relation to changing a column name. – Baklava

Answer 8

For me, When I faced this exception, I solve it using the Django dbshell utility or any kind of MY_DATABASE Viewer / interactive command line.

DBShell:

1. python manage.py dbshell
2. ALTER TABLE [name_of_field_that_already_exists] DROP column [field_table];

Simonsen

Answer 9

Django provides a --fake-initial option which I found effective for my use. From the Django Migration Documentation:

--fake-initial

Allows Django to skip an app’s initial migration if all database tables with the names of all models created by all CreateModel operations in that migration already exist. This option is intended for use when first running migrations against a database that preexisted the use of migrations. This option does not, however, check for matching database schema beyond matching table names and so is only safe to use if you are confident that your existing schema matches what is recorded in your initial migration.

For my use, I had just pulled a project from version control and was preparing to add some new model fields. I added the fields, ran ./manage.py makemigrations and then attempted to run ./manage.py migrate which threw the error since, as one would expect, many of the fields already existed in the existing database.

What I should have done was to run makemigrations immediately after pulling the project from versions control to create a snapshot of existing models' state. Then, running the ./manage.py migrate --fake-initial would be the next step.

After that you can add away and makemigrations > migrate as normal.

NOTE: I do not know if a --fake-initial would skip existing fields and add new ones. I opted to comment out the new fields I'd created up to that point, run the --fake-initial as if it were the first thing I did after pulling from version control, then added in updated fields in the next migration.

Other related documentation: https://docs.djangoproject.com/en/dev/topics/migrations/#initial-migrations

Aposematic

Answer 10

I've been dealing with this for several years. There could be different scenarios:

Scenario 1: as in the original post, you had no tables to start with. In this case, I'd

1. comment out the relationship in models.py
2. run python manage.py
3. migrate assuming that it now succeeds
4. uncomment what you
5. commented out in step 1 run python manage.py migrate --fake

Scenario 2: multiple apps: One possibility is that you might have different apps and the data model of one app is using some tables from the other app. In this case, if the data model is designed properly, you should be able to create the tables for one app only (by specifying only that one in setting.py), then add the other app and migrate. If it is not design with care, and there are recursive dependencies, I suggest changing the design rather than making a temporary fix.

Scenario 3: you had some tables and something went wrong with your migration, then I'd

1. revert models.py to what it was and only introduce the new relationship that appears to already exist in models.py.
2. delete migration folder
3. run python manage.py makemigrations
4. introduce new changes to models.py if any and continue with makemigrations and migrate commands as usual.

Tennyson

Answer 11

I found and solved a particular example of this error in a Django 1.10 project while I was changing a foreign key field named member to point to a different table. I was changing this in three different models and I hit this error on all of them. In my first attempt I renamed member to member_user and tried to create a new field member as a foreign key pointing at the new table, but this didn't work.

What I found is that when I renamed the member column it did not modify the index name in the form <app>_<model>_<hash> and when I tried to create a new member column it tried to create the same index name because the hash portion of the name was the same.

I resolved the problem by creating a new member_user relation temporarily and copying the data. This created a new index with a different hash. I then deleted member and recreated it pointing at the new table and with it the would-be conflicting index name. Once that was done I ran the RunPython step to populate the new member column with references to the applicable table. I finished by adding RemoveField migrations to clean up the temporary member_user columns.

I did have to split my migrations into two files because I received this error:

psycopg2.OperationalError: cannot ALTER TABLE "<table_name>" because it has pending trigger events

After the creation and copy of data into member_user I was not able to remove member in the same migration transaction. This may be a postgres specific limitation, but it was easily resolved by creating another transaction and moving everything after creating and copying member_user into the second migration.

Hiroko

Answer 12

I recently had the same issue and tried some of the solutions here. manage.py migrate --fake led to a "django_content_type" does not exist error. Equally deleting old migrations might cause problems for other users if the migrations are shared.

The manage.py squashmigrations command (docs) seems to be the ideal way to deal with this. Condenses old migrations into a single migration (which prevents applying them out of sequence etc), and preserves the old migrations for any other users. It worked in my case at least.

Calcine

Answer 13

I found this problem in web2pyframework in models/config.py .

Change

settings.base.migrate = True

on config file to

settings.base.migrate = False

Problem solved.

Gaynell

Answer 14

I am not sure about using the solution with fake. Most likely that error will occur again at the next migration.

Find out which columns are creating that problem

1. python manage.py dbshell
2. select * from <tablename> where false; (Now you see which columns are saved by postgresql and can delete them in the database)
3. alter table <tablename> drop column <columnname>; (Start the migrations process)
4. python manage.py makemigrations
5. python manage.py migrate

Christachristabel

Answer 15

If you're getting this error when you run python manage.py test --k, you can fix it by deleting the test database: python manage.py test

Labrie

Answer 16

I had the same problem. ./manage.py migrate --fake is not an option, expecially when it is your first commit. When you run ./manage.py makemigrations it collects a migration file and if it is the first mention of your model in code, then django will try to create such table in DB. To cure it, you should:

1. Remove migrations.CreateModel operations from your migration file
2. Run ./manage.py migrate in console
3. Ctrl+z your changes in mogrations file

Tolbert

Answer 17

That is probably because you have renamed the model property or did not run full migration before.

Use your magic SQL skills to delete the table (or rename it if it's prod) and run the migration.

Remember to make sure that full migration files are consistent and can deploy on a fresh and empty DB.

Blanche

Answer 18

I had a similar problem when I had 2 copies of a project, but they were connected to the same database! In one version I went further and already had some new fields in the database, but in the other version I did not, so the solution was to connect to another database

Cami

Answer 19

Very Easy Solution

TLDR:

Comment out the fields in your migration file that are seen in the already exists error. Then run

python manage.py migrate

Uncomment the fields in the migration files after the migration has successfully run and bam, THAT'S IT. Django now skips all the fields that already exist in your Database, and just adds the new ones.

Inverson

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Pallor

Answer 20

Do not try to use --fake, because with this you risk corrupting your database.

Instead, you can backup your current database, recreate it, and then apply backup.

1. Create backup: pg_dump -F c -v -h localhost <database_name> -f tmp/<pick_a_file_name>.psql

2. Recreate it: rails db:drop db:create

3. Restore backup: pg_restore --exit-on-error --verbose --dbname=<database_name> tmp/<pick_a_file_name>.psql

Provoke