This content originally appeared on DEV Community and was authored by Franck Pachot
It is a good idea to prefer UPDATE ... RETURNING instead of a two-statement transaction with an UPDATE and a SELECT. By default, the new value is returned. Oracle 23c has added the possibility to return the old values.
Here is how to do the same in YugabyteDB and PostgreSQL.
yugabyte=# create table demo (id bigint primary key, value text);
CREATE TABLE
yugabyte=# insert into demo
select generate_series(1,50), '...old...';
INSERT 0 50
yugabyte=# update demo set value='...new...' where id=42
returning id, value;
id | value
----+-----------
42 | ...new...
(1 row)
UPDATE 1
Here is how I run the same to return the old and new values:
yugabyte=# with old as (select * from demo where id=42)
update demo new set value='...new...'
from old where old.id=new.id
returning new.id, old.value, new.value ;
id | old_value | new_value
----+-----------+-----------
42 | ...old... | ...new...
(1 row)
UPDATE 1
The WITH clause (also called CTE for Common Table Expression) is very helpful to do multiple reads and write operations within one statement. The advantage of declaring the logic in a single SQL statement makes it more efficient and scalable by reducing the roundtrips, increasing the possibilities of transparent retries (on clock skew for example) and avoiding anomalies even in read committed isolation level.
This content originally appeared on DEV Community and was authored by Franck Pachot
Franck Pachot | Sciencx (2023-06-04T20:36:42+00:00) UPDATE RETURNING OLD | NEW in YugabyteDB and PostgreSQL. Retrieved from https://www.scien.cx/2023/06/04/update-returning-old-new-in-yugabytedb-and-postgresql/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.