IntegrityError重复键值违反唯一约束 - django / postgres [英] IntegrityError duplicate key value violates unique constraint - django/postgres

问题描述

我正在关注我以前问过的问题，我试图寻求从愚蠢/写得不好的mysql查询转换为postgresql。我相信我成功了。无论如何，我正在使用从mysql数据库手动移动到postgres数据库的数据。我使用的查询如下所示：

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

我有理由相信这样做很好。但是，这也带来了一个新的问题。当尝试提交时，我从django收到错误，指出：

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"

我已经看过这里发布的几个响应我没有找到我的问题的解决方案（尽管有关的问题已经解决了一些有趣的阅读）。我在我的日志中看到这个，这很有趣，因为我从来没有明确地调用insert-django必须处理它：

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(

我已经检查了序列等等，它们似乎是按顺序在这一点上，我不知道该怎么做 - 我认为这是事情django的结局，但我不确定。任何反馈将不胜感激！

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!

推荐答案

这对我来说 - 事实证明，你需要重新同步你的主键字段在postgres - 查看我在这里发贴 - 关键是SQL语句：

This happend to me - it turns out you need to Resync your primary key fields in postgres - check out my post here - the key is the SQL statement:

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

这篇关于IntegrityError重复键值违反唯一约束 - django / postgres的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！