On Aug 29, 2006, at 4:46 , Peter Eisentraut wrote:
> Naz Gassiep wrote:
>> conwatch=# UPDATE replies SET rgt = rgt + 2 WHERE postid = 18 AND rgt
>>> = 11;
>> ERROR: duplicate key violates unique constraint "replies_rgt_postid"
>
> This is a well-known deficiency in PostgreSQL. You will have to work
> around it somehow (by changing the query, the schema, or the index).
One such workaround is:
BEGIN;
UPDATE replies
SET rgt = -1 * (rgt + 2)
WHERE postid = 18
AND rgt >= 11;
UPDATE replies
SET rgt = -1 * rgt
WHERE rgt < 0;
COMMIT;
Michael Glaesemann
grzm seespotcode net