Re: Order of update

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(dot)klaver(at)aklaver(dot)com