Understanding Transactions And Locking In PostgreSQL

Getting Started: A Real World Example

Consider an accounting system; you have debits, credits and will need functions to determine if the user has a positive balance left.

Consider the schema:

create table accounting (
    transaction_id  serial primary key not null,
    account_id      varchar(255) not null,
    debit           numeric(22,9),
    credit          numeric(22,9)
);
 
insert into accounting (account_id, credit) values ('someuser', 100);
insert into accounting (account_id, debit) values ('someuser', 1);

This user should now have a positive account balance of 99.

SELECT (SUM(COALESCE(credit,0)) - SUM(COALESCE(debit,0))) AS balance
    FROM accounting
    WHERE account_id = 'someuser';

The above statement will return the user’s account balance (99).

Note: The coalesce() function returns the first non-null value. In this case if debit or credit are null values, 0 is returned.

• Next: Automation With PLPGSQL