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. The key is the SQL statement:
SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1);
这篇关于IntegrityError 重复键值违反了唯一约束 - django/postgres的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!