Get the difference for a column value by all its previous rows data

[kalyn]

Please consider these two tables:

1. Vault

   • id BIGSERIAL PRIMARY KEY
   • name VARCHAR(30) NOT NULL

2. Transaction

   • id BIGSERIAL PRIMARY KEY
   • transaction_type VARCHAR(6) NOT NULL -- Choice between "credit" or "debit"
   • amount NUMERIC(15,6) NOT NULL
   • vault_id BIGINT REFERENCES Vault (id)

When getting Transaction row/s, I would like to get an extra column for the remaining vault balance after the transaction is done.

Example of Transaction:

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
5       credit                  400         1               500
12      debit                   200         1               300
20      credit                  100         1               400

I can add a column to hold the vault balance and use that for the next transaction. But suppose the user deletes a row, for example row id 5 which has credit amount of 400 is deleted. Then its following transactions vault balance should change accordingly. So for row 12 the vault balance would be -100, and row id 20's vault balance would be 0.

id      transaction_type        amount      vault_id        vault_balance
1       credit                  100         1               100 // since there was no prev transaction for this vault
12      debit                   200         1               -100
20      credit                  100         1               0

The way I see it I have two ways:

• Add a column to store the vault balance in a transaction, and if there is a change, all of its following transaction's vault balance should be updated accordingly.
• Maybe there's a way to get the difference of credit and debit between all of its previous and the current transaction amount.

But I am hoping there is a much better way? Could you please help me what would be most efficient way to handle such situation?

Update

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=2e3312565b94473184dc14e4d5daefff to the Table fiddle.

[Anderson]

Something like this, may be?

sum(case transaction_type when 'debit' then -1 else 1 end * amount)
over (partition by vault_id order by id rows unbounded preceding and current row)