public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter J. Holzer <[email protected]>
To: [email protected]
Subject: Order of update
Date: Sun, 20 Apr 2025 11:10:33 +0200
Message-ID: <[email protected]> (raw)

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-postgresq...

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | [email protected]         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"


Attachments:

  [application/pgp-signature] signature.asc (833B, 2-signature.asc)
  download

view thread (7+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected]
  Subject: Re: Order of update
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox