Is it possible (how?) to return old value from "after update" trigger [duplicate]

Question

UPDATE "Users" 
SET "displayName" = 'new_name' 
WHERE id = 1 
RETURNING "displayName"

Query above returns NEW value. I want to know OLD value of displayName after update. I know that it possible to:

 UPDATE ... 
 FROM SELECT ... 
 RETURNING ...

but I want to use AFTER UPDATE trigger. Is it possible to "return" old value from AFTER UPDATE trigger somehow? If yes how such trigger body will look like?

Answer 1

Yes it is, but no triggers are involved here at all. Just a plain UPDATE.

The RETURNING clause returns values based on the row after the update. But you can include the old row in a FROM clause and use that. Per documentation:

Any expression using the table's columns, and/or columns of other tables mentioned in FROM, can be computed. The new (post-update) values of the table's columns are used.

Solution:

UPDATE "Users" x
SET    "displayName" = 'new_name' 
FROM  (SELECT id, "displayName" FROM "Users" WHERE id = 1 FOR UPDATE) y 
WHERE  x.id = y.id
RETURNING y."displayName"

Detailed explanation:

• Return pre-UPDATE Column Values Using SQL Only - PostgreSQL Version

What if my ORM does not allow a FROM clause?

Are you sure? If so, a simple (more expensive) alternative would be to run two statements:

BEGIN;
-- Retrieve old value first;
SELECT "displayName" FROM "Users" WHERE id = 1 FOR UPDATE;

UPDATE "Users"
SET    "displayName" = 'new_name' 
WHERE  id = 1;

COMMIT;

The transaction wrapper (BEGIN; ... COMMIT;) and the lock on the row (FOR UPDATE) are to defend against possible concurrent writes. You can remove both if you are the only one writing to that table or if an occasional outdated "old value" is ok. Rarely happens except in very competitive setups.

Or use raw SQL to bypass your inferior ORM.

I can imagine tricks to work around this (like adding a redundant column with the pre-update value of "displayName"), but such trickery sounds like a very bad idea. Just use the standard functionality.