Consistency

A wallet system must always keep balances correct and never allow invalid states. PostgreSQL helps enforce this using constraints, but safe operations still depend on how queries are written.

The balance column is protected by a rule that prevents it from going below zero. If a query tries to violate this, PostgreSQL stops it.

Example of an invalid update:
UPDATE accounts
SET balance = -100
WHERE name = 'Alice';

This fails because the database constraint blocks negative values.

Another invalid case:
UPDATE accounts
SET balance = balance - 1500
WHERE name = 'Alice';

If the balance is only 1000, this also fails for the same reason. These errors are enforced by the database schema itself.

However, transferring money is more complex and must be handled carefully.

A correct transfer must run inside a transaction:
UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice' AND balance >= 300;

UPDATE accounts
SET balance = balance + 300
WHERE name = 'Bob';

The condition balance >= 300 ensures that the deduction only happens if enough money is available. If not, no row is updated and the transaction can be safely stopped.

To make the system safe during concurrent transactions, locking is required,there are 2 types of loocking optimistic locking and pesimistic locking.
Optimistic locking: Assumes no conflict and checks for changes before updating, failing if data was modified.
Pessimistic locking: Locks the data before updating to prevent any concurrent access or conflicts.

SELECT balance FROM accounts
WHERE name = 'Alice'
FOR UPDATE;

UPDATE accounts
SET balance = balance - 300
WHERE name = 'Alice';

UPDATE accounts
SET balance = balance + 300
WHERE name = 'Bob';

This prevents multiple operations from modifying the same account at the same time.

In this system, PostgreSQL constraints prevent invalid data like negative balances, while transactions and locking ensure correct behavior during money transfers. Both are required to maintain consistency and avoid financial errors.