IntegrityError duplicate key value violates unique

Question

I'm following up in regards to a question that I asked earlier in which I sought to seek a conversion from a goofy/poorly written mysql query to postgresql. I believe I succeeded with that. Anyways, I'm using data that was manually moved from a mysql database to a postgres database. I'm using a query that looks like so:

  """
  UPDATE krypdos_coderound cru

  set is_correct = case 
      when t.kv_values1 = t.kv_values2 then True 
      else False 
      end

  from 

  (select cr.id, 
    array_agg(
    case when kv1.code_round_id = cr.id 
    then kv1.option_id 
    else null end 
    ) as kv_values1,

    array_agg(
    case when kv2.code_round_id = cr_m.id 
    then kv2.option_id 
    else null end 
    ) as kv_values2

    from krypdos_coderound cr
     join krypdos_value kv1 on kv1.code_round_id = cr.id
     join krypdos_coderound cr_m 
       on cr_m.object_id=cr.object_id 
       and cr_m.content_type_id =cr.content_type_id 
     join krypdos_value kv2 on kv2.code_round_id = cr_m.id

   WHERE
     cr.is_master= False
     AND cr_m.is_master= True 
     AND cr.object_id=%s 
     AND cr.content_type_id=%s 

   GROUP BY cr.id  
  ) t

where t.id = cru.id
    """ % ( self.object_id, self.content_type.id)
  )

I have reason to believe that this works well. However, this has lead to a new issue. When trying to submit, I get an error from django that states:

IntegrityError at (some url): 
duplicate key value violates unique constraint "krypdos_value_pkey"

I've looked at several of the responses posted on here and I haven't quite found the solution to my problem (although the related questions have made for some interesting reading). I see this in my logs, which is interesting because I never explicitly call insert- django must handle it:

   STATEMENT:  INSERT INTO "krypdos_value" ("code_round_id", "variable_id", "option_id", "confidence", "freetext")
   VALUES (1105935, 11, 55, NULL, E'') 
   RETURNING "krypdos_value"."id"

However, trying to run that results in the duplicate key error. The actual error is thrown in the code below.

 # Delete current coding         CodeRound.objects.filter(object_id=o.id,content_type=object_type,is_master=True).delete()
  code_round = CodeRound(object_id=o.id,content_type=object_type,coded_by=request.user,comments=request.POST.get('_comments',None),is_master=True)
  code_round.save()
  for key in request.POST.keys():
    if key[0] != '_' or key != 'csrfmiddlewaretoken':
      options = request.POST.getlist(key)
      for option in options:
        Value(code_round=code_round,variable_id=key,option_id=option,confidence=request.POST.get('_confidence_'+key, None)).save()  #This is where it dies
  # Resave to set is_correct
  code_round.save()
  o.status = '3' 
  o.save(

I've checked the sequences and such and they seem to be in order. At this point I'm not sure what to do- I assume it's something on django's end but I'm not sure. Any feedback would be much appreciated!

Answer 1

This happend to me - it turns out you need to resync your primary key fields in Postgres. The key is the SQL statement:

SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1)

Answer 2

The solution is that you need to resync your primary key fields as reported by "Hacking Life" who wrote an example SQL code but, as suggested by "Ad N" is better to run the Django command sqlsequencereset to get the exact SQL code that you can copy and past or run with another command.

As a further improvement to these answers I would suggest to you and other reader to dont' copy and paste the SQL code but, more safely, to execute the SQL query generated by sqlsequencereset from within your python code in this way (using the default database):

from django.core.management.color import no_style
from django.db import connection

from myapps.models import MyModel1, MyModel2


sequence_sql = connection.ops.sequence_reset_sql(no_style(), [MyModel1, MyModel2])
with connection.cursor() as cursor:
    for sql in sequence_sql:
        cursor.execute(sql)

I tested this code with Python3.6, Django 2.0 and PostgreSQL 10.

Answer 3

It appears to be a known difference of behaviour between the MySQL and SQLite (they update the next available primary key even when inserting an object with an explicit id) backends, and other backends like Postgres, Oracle, ... (they do not).

There is a ticket describing the same issue. Even though it was closed as invalid, it provides a hint that there is a Django management command to update the next available key.

To display the SQL updating all next ids for the application MyApp:

python manage.py sqlsequencereset MyApp

In order to have the statement executed, you can provide it as the input for the dbshell management command. For bash, you could type:

python manage.py sqlsequencereset MyApp | python manage.py dbshell

The advantage of the management commands is that abstracts away the underlying DB backend, so it will work even if later migrating to a different backend.