Django-Postgres Integrity Error: Duplicate key --- how to fix?

Question

Im getting an Integrity Error when adding a new instance of a model, here's the traceback:

Traceback:
File "/home/robain/webapps/django/lib/python2.6/django/core/handlers/base.py" in get_response
  100.                     response = callback(request, *callback_args, **callback_kwargs)
File "/home/robain/webapps/django/lib/python2.6/django/contrib/admin/views/decorators.py" in _checklogin
  33.             return view_func(request, *args, **kwargs)
File "/home/robain/webapps/django/tmanage/tempManage/src/CTmanage.py" in addCT
  101.             CT.save()
File "/home/robain/webapps/django/lib/python2.6/django/db/models/base.py" in save
  434.         self.save_base(using=using, force_insert=force_insert, force_update=force_update)
File "/home/robain/webapps/django/lib/python2.6/django/db/models/base.py" in save_base
  527.                     result = manager._insert(values, return_id=update_pk, using=using)
File "/home/robain/webapps/django/lib/python2.6/django/db/models/manager.py" in _insert
  195.         return insert_query(self.model, values, **kwargs)
File "/home/robain/webapps/django/lib/python2.6/django/db/models/query.py" in insert_query
  1479.     return query.get_compiler(using=using).execute_sql(return_id)
File "/home/robain/webapps/django/lib/python2.6/django/db/models/sql/compiler.py" in execute_sql
  783.         cursor = super(SQLInsertCompiler, self).execute_sql(None)
File "/home/robain/webapps/django/lib/python2.6/django/db/models/sql/compiler.py" in execute_sql
  727.         cursor.execute(sql, params)
File "/home/robain/webapps/django/lib/python2.6/django/db/backends/util.py" in execute
  15.             return self.cursor.execute(sql, params)
File "/home/robain/webapps/django/lib/python2.6/django/db/backends/postgresql_psycopg2/base.py" in execute
  44.             return self.cursor.execute(query, args)

Exception Type: IntegrityError at /tempManage/addCT/13/34/
Exception Value: duplicate key value violates unique constraint "tempManage_childtemplate_previewPath_key"

I think the autoincrement of the key is out of sync with the instance id's (guess based on other posts), but I'm not sure how to fix it. Any help would be much appreciated!

EDIT: asked for models, here is the model where the error stems from. However it has been running for some time without any problem, so it's unlikely it's caused in the model def...

class ChildTemplate(models.Model):

def get_CTswf_path(self, filename):
    return os.path.join('swf', 'Company_' + str(self.father.company.id), "FT_" + str(self.father.id), 'CT_' + str(self.id), filename)

father = models.ForeignKey('FatherTemplate', unique=False, verbose_name='Father', blank=True, null=True)
childName = models.CharField(max_length=20, blank=False, verbose_name='Child Name')
location = models.ForeignKey(Location, unique=False, blank=True, null=True)
company = models.ForeignKey(Company, unique=False, blank=True, null=True)
isCorporate = models.BooleanField(blank=False, verbose_name='Corporate')
templatePath = models.FileField(upload_to=get_CTswf_path, verbose_name='Path', blank=True, null=True)
previewPath = models.CharField(max_length=200, blank=True, unique=True)
#migrateTest = models.BooleanField()

def __unicode__(self):
    return self.childName

meshantz · Accepted Answer · 2011-03-29T18:54:04.517

I've had this happen when loading backups of databases before. Have no idea why postgres didn't get the sequence properly up to date - probably ran into an error I didn't see - but the solution I've used was to check the maximum value in the primary key field, and then select from the sequence attached to it (manually) to get that sequence in the right place.

You can list all sequences in the database from the command line client (psql) with \ds. There's probably one named something like tempManage_childtemplate_previewPath_id_seq.

Something like this should work. Back up your database before manually altering it!

SELECT max(id) FROM <model_table>;
SELECT * FROM tempManage_childtemplate_previewPath_id_seq;

ALTER SEQUENCE tempManage_childtemplate_previewPath_id_seq RESTART WITH <result of above>;

Just realized, you probably also want to see what's already in the sequence, so I've added a select above too. Check out the last_value entry.

Thanks for the tip! At the risk of frying my table with trial and error attempts, what are the psql commands for checking the maximum value to see if it's trying to duplicate, and to manually change it? — HdN8, Mar 29 '11 at 18:25

score 2 · Answer 2 · answered Mar 29 '11 at 17:56

The only two lines that matter are:

Exception Value: duplicate key value violates unique constraint "tempManage_childtemplate_previewPath_key"
previewPath = models.CharField(max_length=200, blank=True, unique=True)

You have added a unique key to previewPath and are trying to insert a value that already exists. So, you need to figure out why the app is trying to insert duplicate preivewPaths. (I have no idea where you are getting id out sync.)

Django-Postgres Integrity Error: Duplicate key --- how to fix?

2 Answers2

Linked