Double entry bookkeeping is

a set of rules for recording financial information in a financial accounting system in which every transaction or event changes at least two different nominal ledger accounts.

An account can be "debited" or "credited", and the sum of all credits must be equal to the sum of all debits.

How would you implement this in a Postgres database? Specifying the following DDL:

CREATE TABLE accounts(
    account_id serial NOT NULL PRIMARY KEY,
    account_name varchar(64) NOT NULL

CREATE TABLE transactions(
    transaction_id serial NOT NULL PRIMARY KEY,
    transaction_date date NOT NULL

CREATE TABLE transactions_details(
    id serial8 NOT NULL PRIMARY KEY,
    transaction_id integer NOT NULL 
        REFERENCES transactions (transaction_id)
    account_id integer NOT NULL
        REFERENCES accounts (account_id)
    amount decimal(19,6) NOT NULL,
    flag varchar(1) NOT NULL CHECK (flag IN ('C','D'))

Note: the transaction_details table does not specify an explicit debit/credit account, because the system should be able to debit/credit more than one account in a single transaction.

This DDL creates the following requirement: After a database transaction commits on the transactions_details table, it must debit and credit the same amount for each transaction_id, e.g:

INSERT INTO accounts VALUES (100, 'Accounts receivable');
INSERT INTO accounts VALUES (200, 'Revenue');


-- The following must succeed
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '1000'::decimal, 'C');

-- But this must raise some error
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 100, '1000'::decimal, 'D');
    INSERT INTO transactions_details VALUES (DEFAULT, 1, 200, '500'::decimal, 'C');

Is it possible to implement this in a PostgreSQL database? Without specifying additional tables to store trigger states.


2 Answers 2


First, this is exactly the question I had in mind when I asked Modelling constraints on subset aggregates? which is certainly the place to start. That question is more general though than this and so my answer here will have a bit more information regarding practical approaches.

You probably do not want to do this declaratively in PostgreSQL. The only possible declarative solutions either break 1NF or are extremely complicated and so this means doing it imperatively.

In LedgerSMB we expect to do this enforcement in two stages (both of which are strict).

  1. All journal entries will come in via stored procedures. These stored procedures will accept a list of line items as an array and check that the sum is equal to 0. Our model in the db is that we have a single amount column with negative numbers being debits and positive numbers being credits (if I was starting over, I would have positive numbers as debits and negative numbers as credits because this is just a little more natural but the reasons here are obscure). Debits and credits are merged on storage and separated on retrieval by the presentation layer. This makes running totals a lot easier.

  2. We will use a deferred constraint trigger which will check on commit based on the system fields on the table. This means that the lines entered in a given transaction must balance, but we can do this beyond the lines themselves.

  • BTW, if you are doing a lot of double entry accounting, e are expecting to re-engineer our financial schema (on postgreSQL) within the next year or so. I don't know if you would be interested in collaborating with an open source project but figured I would extend an invitation. Jun 4, 2013 at 7:36
  • I'm late to the party, but could you please explain - "will check on commit based on the system fields on the table"? Are you using the system field xmin to find out which rows were inserted? I'm faced with this exact situation and this is the only thread that's coming close to a solution. However, I'm ignorant.
    – Code Poet
    Nov 21, 2017 at 4:16
  • Yeah. We can look at the rows created by the transaction and insist that the sum of the amounts in them are 0. That means, basically, checking system columns like xmin. Nov 21, 2017 at 11:08

Another approach is to adopt the position that it is the transfer of the financial amount that comprises a single record.

Thus you might have the structure:

create table ... (
  id                integer,
  debit_account_id  not null REFERENCES accounts (account_id),
  credit_account_id not null REFERENCES accounts (account_id),
  amount            numeric not null);

A check constraint can ensure that the debit and credit accounts are different, and there is only one amount to store. Thus there is guaranteed integrity, which is what the data model ought to provide naturally.

I've worked with systems that adopted this approach successfully. There is a little less efficiency in querying for any records against a particular account, but the table was more compact and queries for an account as the debit only or as the credit only were a little more efficient.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.