How to Retrieve Old Column Values After an UPDATE in PostgreSQL Using Only SQL?

How to Obtain Pre-Update Column Values Using SQL Only

Problem:

PostgreSQL does not allow retrieving pre-update column values in the RETURNING clause of an UPDATE statement. This poses a challenge when seeking to obtain the old values of updated columns without employing additional mechanisms like triggers or procedures.

Solution Using a Self-Join:

One solution is to leverage a self-join in the FROM clause. This technique effectively creates a copy of the table and allows for referencing both the updated and pre-update values:

UPDATE tbl x
SET    tbl_id = 23
     , name = 'New Guy'
FROM   tbl y                -- Using the FROM clause
WHERE  x.tbl_id = y.tbl_id  -- Must be UNIQUE NOT NULL
AND    x.tbl_id = 3
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

This method requires a UNIQUE NOT NULL column for self-joining, ensuring that each row is matched to a single counterpart in the joined instance.

Solutions for Concurrent Write Load:

In scenarios involving heavy write concurrency, two options exist for preventing race conditions:

Option 1: SERIALIZABLE Isolation Level:

Utilizing the SERIALIZABLE isolation level ensures exclusive access to rows during transaction execution, thereby preventing concurrent writes:

BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE ... ;
COMMIT;

Option 2: Explicit Locking:

Alternately, explicit locking can be employed to prevent concurrent updates of rows:

UPDATE tbl x
SET    tbl_id = 24
     , name = 'New Gal'
FROM  (SELECT tbl_id, name FROM tbl WHERE tbl_id = 4 FOR UPDATE) y 
WHERE  x.tbl_id = y.tbl_id
RETURNING y.tbl_id AS old_id, y.name AS old_name
        , x.tbl_id          , x.name;

The above is the detailed content of How to Retrieve Old Column Values After an UPDATE in PostgreSQL Using Only SQL?. For more information, please follow other related articles on the PHP Chinese website!