unique contraint violation in update-from statement

Question

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?

Answer 1

# 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"