On 4/20/25 02:10, Peter J. Holzer wrote:
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.
My read of this is that for the duration of the query a temporary table
a is create that is ordered on `id desc` and that '... from a where t.id
= a.id' will apply that order to the selection of t.id.
As example:
create table id_update(id integer primary key);
insert into id_update select a from generate_series(1, 100000) as t(a);
INSERT 0 100000
-- id(s) are temporarily in order.
update id_update set id = id where id between 50000 and 60000;
UPDATE 10001
-- The above move the 10001 values to 'end' of id_update
with a as (select id from id_update where id > 100 order by id desc)
update id_update as t set id = a.id + 1 from a where t.id = a.id;
UPDATE 99900
-- The UPDATE works even though the t.id(s) in id_update are not ordered
-- by id
So, is there a better way?
hjp
[1]
https://www.cybertec-postgresql.com/en/comparison-of-the-transaction-systems-of-oracle-and-postgresql/
--
Adrian Klaver
adrian.kla...@aklaver.com
