IntegrityError duplicate key value violates unique constraint - django/postgres

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);