CODEWITHSUNDEEP
sqlpostgresql
Truong Hua
Truong Hua

Reputation: 812

PostgreSQL duplicate key when update non-unique column

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();

Upvotes: 0

Views: 1047

Answers (1)

Truong Hua
Truong Hua

Reputation: 812

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.

Upvotes: 1

Related Questions