PostgreSQL：重复的键值违反了UPDATE命令的唯一约束 [英] PostgreSQL: duplicate key value violates unique constraint on UPDATE command

问题描述

在执行UPDATE查询时，我们收到以下错误消息：

When doing an UPDATE query, we got the following error message:

ERROR:  duplicate key value violates unique constraint "tableA_pkey"
DETAIL:  Key (id)=(47470) already exists.

但是，我们的UPDATE查询不会影响主键。这是一个简化的版本：

However, our UPDATE query does not affect the primary key. Here is a simplified version:

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

哪个会产生：

    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

寻找最大表索引：

select max(id) from tableA;

我们得到了一个较低的值：

We got a lower value:

  max  
-------
 50363
(1 row)

您对为什么这样的行为有任何想法吗？如果我们排除了有问题的ID，它就会起作用。

Have you any idea on why such a behavior? If we exclude the problematic id, it works.

另一个奇怪的地方是，将以前的UPDATE替换为：

Another strange point is that replacing the previous UPDATE by:

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;

效果很好。

编辑：触发器

我没有用户-在该表上定义的触发器：

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
;

不返回任何行。

注意：我正在使用 psql（PostgreSQL）9.3.4 版本。

推荐答案

不确定原因是什么。但是，删除与现有ID（？）对应的两个（非重要）记录可以解决此问题。

Not really sure what was the cause. However, deleting the two (non vital) records corresponding to already existing ids (?) solved the issue.

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