PostgreSQL duplicate key when update non-unique column

Question

I run this query bellow on PostgreSQL 9.3.2. Can anyone tell me why this query bellow throw unique violation exception while I do not update the unique column.

The contents table also have two trigger but it just insert and update other table.

Execute command failed: ERROR:  duplicate key value violates unique constraint "contents_0_uk"
DETAIL:  Key (hash)=(\x145806e0794729ba98f16e4e8ec723cb) already exists.
CONTEXT:  SQL statement "UPDATE contents cont
    SET     content         = tmp.content,
            primitive       = tmp.primitive,
            lang            = ARRAY[lang_id(tmp.lang[1]), lang_id(tmp.lang[2]), lang_id(tmp.lang[3])],
            percent         = tmp.percent,
            score           = tmp.score
    FROM tmp_post_bulk tmp
    WHERE tmp.cid = cont.id
            AND tmp.chash_matched = TRUE
            AND (
                    cont.content            != tmp.content
                    OR cont.primitive       != tmp.primitive
                    OR cont.percent         != tmp.percent
                    OR cont.score           != tmp.score
                    OR cont.lang            != ARRAY[lang_id(tmp.lang[1]), lang_id(tmp.lang[2]), lang_id(tmp.lang[3])]
            )"
PL/pgSQL function content_bulk() line 53 at SQL statement
! Query is: SELECT content_bulk();

Maybe the contents_0_uk constraint is on the tables updated by the triggers? Can you post the definition of the table? — Kouber Saparev
– Kouber Saparev, Commented Mar 12, 2014 at 10:03
contents_0_uk is on contents_0 which is a partition of contents table. Trigger do not update any partition in contents table and the updated table do not have any trigger. — Truong Hua
– Truong Hua, Commented Mar 12, 2014 at 10:07
Well, double check the triggers. Something is altering the hash column behind your back. Is that column part of the contents table? — Kouber Saparev
– Kouber Saparev, Commented Mar 12, 2014 at 10:26

Community · Accepted Answer · 2017-05-23 12:27:07Z

1

I have just find out the problem is the same as Postgres, duplicate unique index

Because there are duplicateds row in this table (unique column duplicated), so when the query update the duplicated rows, PostgreSQL throw exception.

I will manual remove the duplicated rows.

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Mar 12, 2014 at 10:59

Truong Hua

8227 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL duplicate key when update non-unique column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related