>_ IzzyLab

Hands-on task

This task is step 14 of the course “PostgreSQL: from your first database to transactions”

PostgreSQL: transferring money in a transaction

medium~7 min

In the shop database there is a table accounts(id, owner, balance) with a CHECK (balance >= 0) constraint: alice = 100, bob = 0. Transfer 30 from alice to bob in a single transaction (BEGIN … COMMIT, two UPDATEs). Result: alice = 70, bob = 30.

Write the transaction as the script /root/transfer.sql in the Editor tab and run it in the shop database — the whole file goes through in one psql session.

What you'll practise:
  • postgresql
  • transactions
  • begin/commit
  • update
  • check constraint

Topic breakdown

A transaction in PostgreSQL is a way to execute several operations as a single indivisible whole: between BEGIN and COMMIT the changes are either all applied at once or not applied at all. The key idea of a money transfer is atomicity: the debit and credit must either both happen or both roll back (ROLLBACK), otherwise money disappears or doubles. A constraint like CHECK (balance >= 0) works at the DBMS level and will fail the transaction if it violates the invariant — the database, not the application, guarantees integrity. An important psql subtlety: commands sent separately each commit on their own, so the whole transaction must run in a single session.

What's checked

  1. alice is left with 70
  2. bob now has 30
  3. both UPDATEs ran in one transaction
  4. the transfer is a transaction in /root/transfer.sql

What it looks like