Duplicate key value violates unique constraint on database initializzation

Question

I have a model for a blog where I want to set the finished field equals True if the other fields are not empty. I populate the database (Postgres) using a script but somethings is not working on initializzation (database is empty, but after migration, so the tables exist).

my models.py:

class Post(models.Model):
    tags = TaggableManager(blank=True)
    ...

    def save(self, *args, **kwargs):
        super(Post, self).save(*args, **kwargs)
        if self.title_it!='' and self.title_en!='' and self.text_it!='' and self.text_en!='' and self.tags!='':
            self.finished=True
            super(Post, self).save(*args, **kwargs)

My script init.py:

def add_post(author, title_it, title_en, text_it, text_en, created_date, 
    published_date, tags, views):
    p = Post.objects.get_or_create(author=author, title_it=title_it, 
        title_en=title_en, text_it=text_it, text_en=text_en, 
        created_date=created_date, published_date=published_date, 
        views=views)[0]
    for t in tags:
        p.tags.add(t)
    p.save()
    return p

and the error when I run the script:

django.db.utils.IntegrityError: ERROR: duplicate key value violates unique constraint "blog_post_pkey"
DETAIL:  Key (id)=(1) already exists.

Here the full traceback:

Traceback (most recent call last):
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 487, in get_or_create
    return self.get(**lookup), False
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 403, in get
    self.model._meta.object_name
blog.models.DoesNotExist: Post matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 85, in _execute
    return self.cursor.execute(sql, params)
psycopg2.IntegrityError: ERRORE:  un valore chiave duplicato viola il vincolo un
ivoco "blog_post_pkey"
DETAIL:  La chiave (id)=(1) esiste già.


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "init_poss.py", line 8374, in <module>
    populate()
  File "init_poss.py", line 7311, in populate
    ['Servizio'], 1) #tzinfo=<UTC>
  File "init_poss.py", line 8323, in add_post
    views=views)[0]
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\manager
.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 489, in get_or_create
    return self._create_object_from_params(lookup, params)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 528, in _create_object_from_params
    raise e
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 521, in _create_object_from_params
    obj = self.create(**params)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 417, in create
    obj.save(force_insert=True, using=self.db)
  File "D:\progetti\possedimenti\sitopossedimenti\blog\models.py", line 58, in s
ave
    super(Post, self).save(*args, **kwargs)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\base.py
", line 729, in save
    force_update=force_update, update_fields=update_fields)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\base.py
", line 759, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, upda
te_fields)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\base.py
", line 842, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\base.py
", line 880, in _do_insert
    using=using, raw=raw)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\manager
.py", line 82, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.p
y", line 1125, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\sql\com
piler.py", line 1283, in execute_sql
    cursor.execute(sql, params)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 100, in execute
    return super().execute(sql, params)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 68, in execute
    return self._execute_with_wrappers(sql, params, many=False, executor=self._e
xecute)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 77, in _execute_with_wrappers
    return executor(sql, params, many, context)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 85, in _execute
    return self.cursor.execute(sql, params)
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\utils.py", lin
e 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\backends\utils
.py", line 85, in _execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: ERRORE:  un valore chiave duplicato viola il vin
colo univoco "blog_post_pkey"
DETAIL:  La chiave (id)=(1) esiste già.

Answer 1

Here :

def save(self, *args, **kwargs):
    super(Post, self).save(*args, **kwargs)
    if self.title_it!='' and self.title_en!='' and self.text_it!='' and self.text_en!='' and self.tags!='':
        self.finished=True
        super(Post, self).save(*args, **kwargs)

You are calling super().save() a second time with the same kwarg. since the force_insert arg is set to True as you can see from the traceback:

File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.py", line 521, in _create_object_from_params
    obj = self.create(**params)
File "D:\progetti\Envs\possedimenti\lib\site-packages\django\db\models\query.py", line 417, in create
    obj.save(force_insert=True, using=self.db)

you end up asking the ORM to create a second record, and since by that time the pk has been set (by the first super.save() call), you indeed get a unique constraint violation.

You could try and start messing with kwargs but that's actually a bad idea (better leave those flags to the ORM) - the simple solution is to make sure you call super.save() only once:

def save(self, *args, **kwargs):
    # non-empty strings have a true value
    # so no need to explicitely test against
    # the empty string.
    # Note that this test will probably not
    # behave how you expect with strings containing
    # only space characters but that was your original
    # code behaviour too so I left this alone.
    self.finished = (
      self.title_it and self.title_en 
      and self.text_it and self.text_en 
      and self.tags
      )
    super(Post, self).save(*args, **kwargs)

Edit: since self.tags is actually a related field (from the taggit app) you can't test self.tags unconditionnally as you need the instance to have been saved in db before you can access any related object. The solution here is to test against self.pk first and only test the other fields if relevant:

def save(self, *args, **kwargs):
    if self.pk:
        self.finished = (
          self.title_it and self.title_en 
          and self.text_it and self.text_en 
          # unless `taggit` does some weird things
          # wrt/ tags storage, this should be the 
          # right test
          and self.tags.exists()
          )
    else:
        # no pk so no flags so it can not be finished
        self.finished = False

    # and call `super.save()` whatever the case
    super(Post, self).save(*args, **kwargs)