Savepoints and rolling back

Continuing to think about the amount of maturing time deposits in the near future. The ones over 250K have the most impact on the outcomes seen during the 2008 market.

RCONHK12 (>=250k) stores those maturing in the next three months and RCONHK13 (>=250k) stores those expiring between 3 and 12 months. If these are higher than $1 million dollars it can cause a funds shortage at a bank as these are typically larger customers of the bank who might also pull other assets. Again, there is a positive factor if these are less than $500K.

I've made a few mistakes in my code by setting the wrong value for those over $500 thousand!

This exercise is part of the course

Transactions and Error Handling in PostgreSQL

View Course

Exercise instructions

Within a transaction set FIELD48 to mature+ if total maturing deposits is less than $500 thousand and set a savepoint matureplus_flag_set.
Set FIELD48 to mature- if total maturing deposits is between $500 thousand and $1 million then set a savepoint matureminus_flag_set.
Set FIELD48 to mature-- if total maturing deposits is greater than $100 thousand (not $1 million) - "accidentally" omitting a 0.
Undo back to just after we set the mature- records and count all the banks with a mature-- deposit maturity schedule.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

BEGIN;

-- Update FIELD48 to indicate a positive maturity rathing when less than $500 thousand.
UPDATE ffiec_reci 
SET ___ = '___' 
WHERE RCONHK12 + RCONHK13 < 500000;

-- Set a savepoint
SAVEPOINT ___;

-- Update FIELD48 to indicate a negative maturity rathing when between $500 thousand and $1 million.
UPDATE ffiec_reci 
___ 
WHERE RCONHK12 + RCONHK13 BETWEEN ___ AND ___;

-- Set a savepoint
SAVEPOINT ___;

-- Accidentailly update FIELD48 to indicate a double negative maturity rating when more than 100K
UPDATE ffiec_reci 
SET ___ = '___' 
WHERE RCONHK12 + RCONHK13 > 100000;

-- Rollback to before the last mistake
ROLLBACK TO ___;

-- Select count of records with a double negative indicator
SELECT count(FIELD48) 
from ffiec_reci 
WHERE FIELD48 = 'mature--';

Edit and Run Code