PostgreSQL：重复键值违反 UPDATE 命令的唯一约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24447930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: duplicate key value violates unique constraint on UPDATE command
提问by Jonathan Petitcolas
When doing an UPDATE query, we got the following error message:
在执行 UPDATE 查询时，我们收到以下错误消息：
ERROR: duplicate key value violates unique constraint "tableA_pkey"
DETAIL: Key (id)=(47470) already exists.
However, our UPDATE query does not affect the primary key. Here is a simplified version:
但是，我们的 UPDATE 查询不会影响主键。这是一个简化版本：
UPDATE tableA AS a
SET
items = (
SELECT array_to_string(
array(
SELECT b.value
FROM tableB b
WHERE b.a_id = b.id
GROUP BY b.name
),
','
)
)
WHERE
a.end_at BETWEEN now() AND now() - interval '1 day';
We ensured the primary key sequence was already synced:
我们确保主键序列已经同步：
\d tableA_id_seq
Which produces:
其中产生：
Column | Type | Value
---------------+---------+--------------------------
sequence_name | name | tableA_id_seq
last_value | bigint | 50364
start_value | bigint | 1
increment_by | bigint | 1
max_value | bigint | 9223372036854775807
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 0
is_cycled | boolean | f
is_called | boolean | t
Looking for maximum table index:
寻找最大表索引：
select max(id) from tableA;
We got a lower value:
我们得到了一个较低的值：
max
-------
50363
(1 row)
Have you any idea on why such a behavior? If we exclude the problematic id, it works.
你知道为什么会有这种行为吗？如果我们排除有问题的 id，它就起作用了。
Another strange point is that replacing the previous UPDATE by:
另一个奇怪的点是将之前的 UPDATE 替换为：
UPDATE tableA AS a
SET
items = (
SELECT array_to_string(
array(
SELECT b.value
FROM tableB b
WHERE b.a_id = b.id
GROUP BY b.name
),
','
)
)
WHERE a.id = 47470;
It works well. Are we missing something?
它运作良好。我们错过了什么吗？
EDIT:triggers
编辑：触发器
I have no user-defined triggers on this table:
我在这个表上没有用户定义的触发器：
SELECT t.tgname, c.relname
FROM pg_trigger t
JOIN pg_class c ON t.tgrelid = c.oid
WHERE
c.relname = 'tableA'
AND
t.tgisinternal = false
;
Which returns no row.
不返回任何行。
Note:I am using psql (PostgreSQL) 9.3.4version.
注意：我使用的是psql (PostgreSQL) 9.3.4版本。
采纳答案by Jonathan Petitcolas
Not really sure what was the cause. However, deleting the two (non vital) records corresponding to already existing ids (?) solved the issue.
不太确定是什么原因。但是，删除与现有 ID (?) 对应的两个（非重要）记录解决了该问题。
