In postgres, you don't really need to do CTE - UPDATE can return the new row if you add RETURNING * so you can just do the update and look at result.
In Oracle (and what we did in actual bank) was pl/sql procedure that did
- select for update - do the validation - apply the transfer, or return error. - let the application rollback or commit
This is hard to do without procedural language - you can work with CTEs and functions, but SQL is great at dataflow and bad at control flow, and you either drop to procedural language in database, or have the application do the actual logic.
UPDATE balance SET balance = balance - 100
WHERE user_id = 1 and balance >= 100;
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).
Am I the only one who would consider a CTE for this, selecting the initial balance and the proposed balance minus amount in the CTE, testing it in the query following the CTE and potentially applying it, and then returning the original and new balance to the application. If both are the same then the update was not applied (not enough balance), and if different the update was applied (enough balance for the withdrawal).
This way it all occurs within a single transaction, even though it is several logical steps, and is simple to reason about.