关于我之前提出的问题,我在寻求将一个笨拙/写得很差的mysql查询转换为postgresql。我相信我已经成功了。无论如何,我正在使用手动从mysql数据库移动到postgresql数据库的数据。我正在使用以下查询:
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获得一个错误,提示:
IntegrityError at (some url):
duplicate key value violates unique constraint "krypdos_value_pkey"
我看了这里发布的几个回答,但还没有找到解决我的问题的方法(虽然相关问题很有趣)。我在日志中看到了这个,这很有趣,因为我从来没有显式地调用insert- django必须处理它:
STATEMENT: INSERT INTO "krypdos_value" ("code_round_id", "variable_id", "option_id", "confidence", "freetext")
VALUES (1105935, 11, 55, NULL, E'')
RETURNING "krypdos_value"."id"
然而,尝试运行该代码会导致重复键错误。实际的错误信息在下面的代码中抛出。
# 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端的问题,但我不确定。非常感谢任何反馈!
key[0] != '_' or key != 'csrfmiddlewaretoken'
等同于not (key[0] == '_' and key == 'csrfmiddlewaretoken')
。很容易看出内部条件永远不会被满足,因此它等同于not (False)
,换句话说就是True
。那么为什么还要使用if
呢? - Jonas Kölkerpython manage.py sqlsequencereset <app> | python manage.py dbshell
- user