I've just read Laurenz' blog post about the differences between Oracle
and PostgreSQL[1].
One of the differences is that something like
UPDATE tab SET id = id + 1;
tends to fail on PostgreSQL because the the primary key constraint is
checked for every row, so it will stumble over the temporary conflicts.
The solution is to define the constraint as deferrable.
But that got me to thinking about different ways ...
There won't be a conflict if the ids are updated in descending order.
Is there a way to force PostgreSQL to update the rows in a specific
order?
I came up with
with a as (select id from t where id > 50 order by id desc)
update t set id = a.id+1 from a where t.id = a.id;
which works in my simple test case, but it doesn't look like it's
guaranteed to work. The implicit join in «update t ... from a» could
produce rows in any order, especially for large tables.
So, is there a better way?
hjp
[1] https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresql/
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
