Skip to content

Instantly share code, notes, and snippets.

@prestonp
Created October 16, 2014 17:21
Show Gist options
  • Save prestonp/0b5cd2416b4b3853db3f to your computer and use it in GitHub Desktop.
Save prestonp/0b5cd2416b4b3853db3f to your computer and use it in GitHub Desktop.
postgres update return new values and old values
-- returning updated values and old values by using a sub-expression
update orders
set type = 'delivery'
where id = 3767
returning id, type, (
select type from orders where id = 3767
) as old_type;
@vovanec
Copy link

vovanec commented Feb 23, 2021

This will only work if isolation level is higher than 'read uncommitted', won't work with 'read uncommitted' setting.

@ankitAtVauld
Copy link

So in case of concurrent DB transactions, we are safe to get the last value for this particular update if the Isolation Level is "Read committed" ?

@richmonde
Copy link

How does this make it ?

@richmonde
Copy link

I just asked CHATGPT and it showed the same way as the gist showes, also it's okay to use with clause to make temp table to store the old values or use triggers with OLD keyword , but I think the subquery one is the most convenient way.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment