id autofield not incrementing when id is explicitly defined in postgresql 8.3

[brightyellowmatrix@…]

I'm using postgresql 8.3 and psycopg2-2.0.8-0ubuntu2. I found the same bug in django release 1.02 and svn revision 10941
The interesting thing is that this doesn't happen using sqlite3. With SQLite, after the initial insert with the explicit id, new rows have ids starting at 6.

Here is my model:

#my models.py file
from django.db import models
class SimpleModel(models.Model):
        value = models.CharField(max_length=128)

Here is my loader

#my testload.py file
from django.core.management import setup_environ
from testproject import settings

setup_environ(settings)
from testproject.test_app.models import SimpleModel

sm = SimpleModel(id=5,value="Hello World")
sm.save()

for i in range(50):
  sm = SimpleModel(value="%sHello World"%i)
  sm.save()

Here is the subsequent stack trace:

Traceback (most recent call last):
  File "testload.py", line 13, in <module>
    sm.save()
  File "/usr/lib/python2.6/dist-packages/django/db/models/base.py", line 410, in save
    self.save_base(force_insert=force_insert, force_update=force_update)
  File "/usr/lib/python2.6/dist-packages/django/db/models/base.py", line 486, in save_base
    result = manager._insert(values, return_id=update_pk)
  File "/usr/lib/python2.6/dist-packages/django/db/models/manager.py", line 177, in _insert
    return insert_query(self.model, values, **kwargs)
  File "/usr/lib/python2.6/dist-packages/django/db/models/query.py", line 1087, in insert_query
    return query.execute_sql(return_id)
  File "/usr/lib/python2.6/dist-packages/django/db/models/sql/subqueries.py", line 320, in execute_sql
    cursor = super(InsertQuery, self).execute_sql(None)
  File "/usr/lib/python2.6/dist-packages/django/db/models/sql/query.py", line 2369, in execute_sql
    cursor.execute(sql, params)
  File "/usr/lib/python2.6/dist-packages/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: duplicate key value violates unique constraint "test_app_simplemodel_pkey"

Here's what makes it into the database before the error. Notice that the id:5 "Hello World" is there with the proper ids. and the rows after that have ids starting at 1. In SQLite the new ids start at 6.

       id       value
        5	Hello World
	4	3Hello World
	3	2Hello World
	2	1Hello World
	1	0Hello World

Here's the most relevant documentation
​http://docs.djangoproject.com/en/dev/ref/models/instances/#s-explicitly-specifying-auto-primary-key-values

My use case is pretty simple. I'm migrating a bunch of objects that already have ids. Once they are migrated, I want to be able to add new ones and have the ids automatically created.

[James Bennett]

When you do this on Postgres, you need to reset the sequence from which automatic values are generated (since that sequence doesn't know that you manually inserted some values, and so those values are no longer available for use); this is a known feature of Postgres, and Django provides ​a management command which will output the appropriate SQL for you to run to reset the sequence.

[brightyellowmatrix@…]

Thanks ubernostrum. I'm going to go think about this some more. I definitely don't find this feature very friendly.

[James Bennett]

Friendly or not, this is the way Postgres, Oracle, DB2 and many other databases work. MySQL and SQLite are really more the exceptions here, since sequences are part of the SQL standard.