Django : ProgrammingError: column "id" does not exist

Question

I try to use postgresql database (before I had SQLite) but I have a message when I execute python manage.py migrate :

Operations to perform:
Apply all migrations: sessions, admin, sites, auth, contenttypes, main_app
Running migrations:
Rendering model states... DONE
Applying main_app.0004_auto_20161002_0034...Traceback (most recent call last):
File "manage.py", line 10, in <module>
execute_from_command_line(sys.argv)
File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 353, in execute_from_command_line
utility.execute()
File "/usr/local/lib/python2.7/dist-packages/django/core/management/__init__.py", line 345, 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 348, in run_from_argv
self.execute(*args, **cmd_options)
File "/usr/local/lib/python2.7/dist-packages/django/core/management/base.py", line 399, in execute
output = self.handle(*args, **options)
File "/usr/local/lib/python2.7/dist-packages/django/core/management/commands/migrate.py", line 200, in handle
executor.migrate(targets, plan, fake=fake, fake_initial=fake_initial)
File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 92, in migrate
self._migrate_all_forwards(plan, full_plan, fake=fake, fake_initial=fake_initial)
File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 121, in _migrate_all_forwards
state = self.apply_migration(state, migration, fake=fake, fake_initial=fake_initial)
File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/executor.py", line 198, in apply_migration
state = migration.apply(state, schema_editor)
File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/migration.py", line 123, in apply
operation.database_forwards(self.app_label, schema_editor, old_state, project_state)
File "/usr/local/lib/python2.7/dist-packages/django/db/migrations/operations/fields.py", line 201, in database_forwards
schema_editor.alter_field(from_model, from_field, to_field)
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/base/schema.py", line 482, in alter_field
old_db_params, new_db_params, strict)
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/postgresql/schema.py", line 110, in _alter_field
new_db_params, strict,
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/base/schema.py", line 634, in _alter_field
params,
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/base/schema.py", line 110, in execute
cursor.execute(sql, params)
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 79, in execute
return super(CursorDebugWrapper, self).execute(sql, params)
File "/usr/local/lib/python2.7/dist-packages/django/db/backends/utils.py", line 64, in execute
return self.cursor.execute(sql, params)
File "/usr/local/lib/python2.7/dist-packages/django/db/utils.py", line 95, 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 64, in execute
return self.cursor.execute(sql, params)
django.db.utils.ProgrammingError: column "id" does not exist
LINE 1: ..._app_projet" ALTER COLUMN "id" TYPE integer USING "id"::inte...

So I guess this error is about my model 'Projet' but this model has ID field ! I see that in 0001_initial.py :

...
migrations.CreateModel(
        name='Projet',
        fields=[
            ('id', models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID')),
            ('name', models.CharField(db_index=True, max_length=30)),
            ('presentation', models.TextField(max_length=2500, null=True)),
...

My migration main_app.0004_auto_20161002_0034 :

class Migration(migrations.Migration):

    dependencies = [
        ('main_app', '0003_auto_20161002_0033'),
    ]

    operations = [
        migrations.AlterField(
            model_name='projet',
            name='id',
            field=models.AutoField(auto_created=True, primary_key=True, serialize=False, verbose_name='ID'),
    ),
    ]

Projet model is like that :

class Group(models.Model) :
    name = models.CharField(max_length=30, db_index=True)

    class Meta :
        abstract = True
        unique_together = (("id", "name"),)


class Projet(Group) :
    presentation = models.TextField(max_length=2500, null=True)

Realy I don't understand why I have this error... my settings configuration database is good, all others models work, ...

If I forgot something, please don't hesitate to tell me ! I need realy your helps !

Answer 1

I have that error happen when I try to use a third party library to upload data to the table with the keyword 'replace'.

For instance if I use pyodbc/sqlalchemy code and pandas together like

df.to_sql(table_name, engine, if_exists='replace')

where table_name is a table in my model.py file.

I do this on initial load as well as periodically for dashboards where I want to use external sources without keeping existing data.

What pandas does is create a primary key column called 'index' instead of id on replace. So, then when I try to use a queryset, it says it can't find the id field because its' name got changed.

I don't know if there is a way using pandas to change the index to id as the default. Perhaps a comment can add flavor. My data doesn't have a true primary key because I have a lot of redundant data in the data for different levels of aggregation.

Answer 2

Do this:

• You need to delete the table from the database.

  DROP TABLE <table>;
  

• Perform migration:

  python manage.py migrate
  

• If this does not help, do the migration using peewee:

  /var/venv3.8/bin/pw_migrate migrate --database=postgresql://postgres@127.0.0.1:5432/<db>
  

Answer 3

if the id column is missing, just add the column in Postgres itself. Go to pgadmin than to the table and there create the column id.

Than back to Django makemigrations than migrate. If this will fail, delete everything in the migration folder of the app without init.py and delete everything in the folder pycache

than makemigrations followed by migrate and it should work.

Answer 4

class Group(models.Model) :
    name = models.CharField(max_length=30, unique=True)

    class Meta:
        abstract = True


class Projet(Group) :
    presentation = models.CharField(max_length=2500, blank=True)  

Remove your migration and create a new one.

Just adding my two cents.

Nullable fields:

Avoid using null on string-based fields such as CharField and TextField because empty string values will always be stored as empty strings, not as NULL. If a string-based field has null=True, that means it has two possible values for “no data”: NULL, and the empty string. In most cases, it’s redundant to have two possible values for “no data;” the Django convention is to use the empty string, not NULL.

See https://docs.djangoproject.com/en/1.10/ref/models/fields/#null

max_length:

If you specify a max_length attribute, it will be reflected in the Textarea widget of the auto-generated form field. However it is not enforced at the model or database level. Use a CharField for that.

https://docs.djangoproject.com/en/1.10/ref/models/fields/#textfield

Answer 5

Your model definition doesn't identify any of the fields as a primary key, therefore Django assumes a primary key column named id. However this column doesn't actually exist in the table.

Change your model definition to designate one of the fields as a primary key, and Django won't try to look for an id column.