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 !

Why do you have `unique_together=((id", "name"),)`? The primary key id is already unique by itself, so it doesn't make sense to include it in `unique_together`. The traceback shows that `0004_auto_20161002_0034` is causing the error, so you should include that migration in your question. — Alasdair, Oct 02 '16 at 09:40
Yes It's true, I remove the unique_together. I included the 0004_auto.. — Preumsse, Oct 02 '16 at 09:59
Did you by any chance make `Group` `abstract` in a migration, when it wasn't `abstract` before? Child models of nonabstract models won't have `id` columns... — AKX, Oct 02 '16 at 10:02
No never, in the initial migration I haven't the model Group because it's abstract and never I remove "abstract" of this model... — Preumsse, Oct 02 '16 at 10:15
Yeah, I resolved my problem : I create a new app (containing model Projet and others models) and I use 'migration --fake'. I didn't create this app just for resolve my problem (It was planned). Thank you everyone for your help ! — Preumsse, Oct 04 '16 at 20:41

John Gordon · Answer 1 · 2016-10-02T00:19:42.797

12

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.

edited Oct 02 '16 at 00:19

answered Oct 01 '16 at 23:25

John Gordon

29,573
7
33
58

But in Django, if I don't create a field as a primary key, Django do it ! The proof : In 0001_initial.py, a primary key was created automatically... – Preumsse Oct 02 '16 at 09:29
Yeah. Django implicitly creates an autoincrementing integer primary key column if none exists, so that's not the issue here. – AKX Oct 02 '16 at 10:00
@AKX Django assumes an `id` column exists, but I don't believe it _creates_ it. – John Gordon Oct 02 '16 at 16:15
I upvoted this answer - now i read this in the Documentation - "An id field is added automatically, but this behavior can be overridden. See Automatic primary key fields." Source = https://docs.djangoproject.com/en/2.1/topics/db/models/ – Rohit Dhankar Feb 02 '19 at 14:37
1

Then django should create that column during migration. – Soerendip Oct 11 '19 at 20:32
It creates it, but when you add the primary key the id column is removed.... – Soerendip Oct 30 '19 at 19:20
@Sören I am also getting this type of error because first I created a model without city field which is a Foreignkey field but after adding this I'm getting the same error because my previous data doesn't know about CITY field, so please help us if you know the solution of this question – Nikhil Bhardwaj Nov 15 '19 at 03:57
I reset the whole database, sorry. Maybe someone else knows. I think the problem is that the id field is completely deleted. – Soerendip Nov 15 '19 at 20:20

Michael Samoylov · Answer 2 · 2016-10-02T11:40:31.143

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

I remove 'default=True' from my model and replace by default="", but I have the same problem — Preumsse, Oct 02 '16 at 10:20
@Preumsse did you solve this error? if yes then please answer it, Thank You — Nikhil Bhardwaj, Nov 15 '19 at 03:54

score 1 · Answer 3 · answered Apr 06 '20 at 11:13

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.

score 0 · Answer 4 · answered Dec 01 '21 at 22:14

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.

score -1 · Answer 5 · edited Jun 08 '21 at 15:40

-1

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>

edited Jun 08 '21 at 15:40

Paul Roub

36,322
27
84
93

answered Jun 08 '21 at 15:29

Ilya Vishinsky

1

Django : ProgrammingError: column "id" does not exist

5 Answers5

Linked