Become Zend Certified

Prepare for the ZCE exam using our quizzes (web or iPad/iPhone). More info...

When you're ready get 7.5% off your exam voucher using voucher CJQNOV23 at the Zend Store

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:

Listing 1 listing-1.sql
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.

Listing 2 listing-2.sql
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.

In This Article