On: PostgreSQL anti-patterns: read-modify-write cycles (2014)

[buro9]

That is why I prefer the CTE approach.

Query 1 (in CTE): Verify all is correct and the current balance (and whether you have enough balance)

Query 2: Perform update and obtain new balance

Then return a summary showing the starting balance and end balance for the transaction (proof of what, if anything, happened).