Django: DatabaseError column does not exist

Question

I'm having a problem with Django 1.2.4.

Here is a model:

class Foo(models.Model):
    # ...
    ftw = models.CharField(blank=True)
    bar = models.ForeignKey(Bar, blank=True)

Right after flushing the database, I use the shell:

Python 2.6.6 (r266:84292, Sep 15 2010, 15:52:39) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
(InteractiveConsole)
>>> from apps.foo.models import Foo
>>> Foo.objects.all()
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 67, in __repr__
    data = list(self[:REPR_OUTPUT_SIZE + 1])
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 82, in __len__
    self._result_cache.extend(list(self._iter))
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/query.py", line 271, in iterator
    for row in compiler.results_iter():
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/compiler.py", line 677, in results_iter
    for rows in self.execute_sql(MULTI):
  File "/usr/local/lib/python2.6/dist-packages/django/db/models/sql/compiler.py", line 732, in execute_sql
    cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/util.py", line 15, in execute
    return self.cursor.execute(sql, params)
  File "/usr/local/lib/python2.6/dist-packages/django/db/backends/postgresql_psycopg2/base.py", line 44, in execute
    return self.cursor.execute(query, args)
DatabaseError: column foo_foo.bar_id does not exist
LINE 1: ...t_omg", "foo_foo"."ftw", "foo_foo...

What am I doing wrong here?

Update: If I comment out the ForeignKey, the problem disappears.

Update 2: Curiously, this unit test works just fine:

def test_foo(self):
    f = Foo()
    f.save()

    self.assertTrue(f in Foo.objects.all())

Why does it work here but not in the shell?

Update 3: The reason it works in unit testing but not the shell may have something to do with the different databases being used:

settings.py:

DATABASES = {
    'default': {
        'ENGINE': 'postgresql_psycopg2',
        'NAME': 'foo',
        'USER': 'bar',
        'PASSWORD': 'baz',
        'HOST': '',
        'PORT': '',
    }
}

import sys
if 'test' in sys.argv or True:
    DATABASES = {
        'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': 'testdb'
        }
    }

Update 4: Confirmed that when I use SQLite3 as the db, everything works fine.

Solution 1

Try completely dropping/wiping the database before running syncdb.

I remember needing to do that a while back when I had made changes to foreign key fields.

Solution 2

Please Read this before you drop your entire DB

I had same issue. Please read the exception completely. I had a ModelForm class that read from my table to build a form and exception was there. I commented out and then run the makemigrations and works completely. After that I commented the ModelForm class and everything is working perfect.

Hope this helps.

Solution 3

I fixed this issue by dropping the specific table to that model of question. Then used:

python manage.py syncdb

If you use PostgreSQL then i recommend using phppgadmin its a web interface similar to PHPmyadmin that is used for mySQL.

Alternative to the user interface you can just do command line

su postgres #change user to postgres
psql <datebase> #access shell for <datebase> database
\d #list all tables
DROP TABLE "" CASCADE #select a table to drop
\q #exit shell

When in \d, escape by pressing q.

Solution 4

if you are using Django 1.8 you should create the column. To make sure you create the column correctly find the migration file in which you created the field and run:

./manage.py sqlmigrate app_name migration_name_sans_extension

This will output the sql commands to create the column. Be sure to include the commands that handle relationships and run the commands in your databse console. You won' have to do anything extreme, like dropping the table or the database.

View more solutions

Comments

[Antoine Pelisse]

It is specified in django documentation that syncdb will not modify existing tables. So if you created the tables with syncdb, and then modified some fields by changing the model, you will need to drop everything: ./manage.py reset myapp would do the trick. It obviously works for unittest as tables are recreated at every run.

[Bjorn]

If you do not want to reset your db after every model change, try South.

[highpost]

Can you say what the column should look like? For example, I have two ForeignKey columns: referred_by and supported_by which both refer to Users. I would then add two more columns: user_referred_by and user_supported_by and they would REFERENCE User(id)?? Can you say where in the 1.8 docs this is covered?

[highpost]

For example, I'm still getting django.db.utils.ProgrammingError: column user.referred_by_id does not exist.

[Ernest Jumbe]

The columns will reference by id unless you specify a to_field field. I'm not sure this is covered in the docs but the information on FK fields is here: docs.djangoproject.com/en/1.8/ref/models/fields/#foreignkey

[Chris]

This should be the accepted answer, since it applies to modern versions of Django and all other answers are really unsuited for working with database tables you really can't afford to drop.