CODEWITHSUNDEEP
postgresqlpostgresql-9.3
user606521
user606521

Reputation: 15454

unique contraint violation in update-from statement

I have following query:

UPDATE "PostsTags"
SET "TagId" = "PostsTags"."TagId" #tgs."mainId"
FROM (
  SELECT t1."TagId" AS "mainId", t1.text AS "mainText", t2."TagId" AS "malformedId", t2.text AS "malformedText"
  FROM "TagTranslations" t1 
  INNER JOIN "TagTranslations" t2 ON t1.text = regexp_replace(t2.text, '^\s+','') AND t2.id != t1.id
  WHERE regexp_replace(t1.text, '^\s+','') = t1.text AND t1."language" = 'en-us'
) tgs
WHERE "PostsTags"."TagId" = tgs."malformedId" AND "PostsTags"."language" = 'en-us' AND "PostsTags"."PostId" = 281
RETURNING *

As you can see I am updating TagId = TagId (I am setting same value to TagId column). I am getting following error:

ERROR:  duplicate key value violates unique constraint "PostsTags_pkey"
DETAIL:  Key (language, "PostId", "TagId")=(en-us, 281, 10157) already exists.

Can someone tell me how it is possible that I am receiving this violations while I am not actually changing anything?

Upvotes: 0

Views: 73

Answers (1)

joop
joop

Reputation: 4523

# does not introduce a comment. It is the bitwise XOR operator. The manual

For comments that end on the end of line, use -- THIS Is a Comment

UPDATE "PostsTags"
SET "TagId" = "PostsTags"."TagId" -- COMMENTED OUT: tgs."mainId"
FROM (
  SELECT t1."TagId" AS "mainId", t1.text AS "mainText", t2."TagId" AS "malformedId", t2.text AS "malformedText"
  FROM "TagTranslations" t1 
  INNER JOIN "TagTranslations" t2 ON t1.text = regexp_replace(t2.text, '^\s+','') AND t2.id != t1.id
  WHERE regexp_replace(t1.text, '^\s+','') = t1.text AND t1."language" = 'en-us'
) tgs
WHERE "PostsTags"."TagId" = tgs."malformedId" AND "PostsTags"."language" = 'en-us' AND "PostsTags"."PostId" = 281
-- RETURNING *
  ;

BTW: there is still no guarantee that the update is correct; the subquery could result in more than one matching tuple being returned for a given t2.TagId --> tgs."malformedId" --> "postTags"."TagId"

Upvotes: 1

Related Questions