--- Phoenix Kiula <phoenix.kiula@gmail.com> wrote:
> Isn't this expected behavior? When you update the m2 of the first
> record, it becomes 2 and violates the unique constraint as the second
> row already has an m2 value of 2.
Well, it is a limitation PostgreSQL. This type of update is should work without any problem
according to the SQL standard. The Problem with performing two updates is that it double the
amount of dead tuples. I guess that this would be another example where having a small fill
factor would help.
One kind of data model that depends heavily on this type of operation is the Hierarchical Nested
Set data model. Inserting/updating/deleting nodes and branches into the table requires updating
the primary key of a lot of records.
Regards,
Richard Broersma Jr.