Postgresql – Calculating balance sheet with a single query

postgresql

I have this schema for double entry bookkeeping system:

CREATE TABLE account (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  parent_id INTEGER REFERENCES account(id),

  UNIQUE (name, parent_id)
);

CREATE TABLE journal (
  id    SERIAL PRIMARY KEY,
  title TEXT NOT NULL,
  date  DATE NOT NULL DEFAULT CURRENT_DATE
);

CREATE TABLE posting (
  id          SERIAL PRIMARY KEY,
  account_id  INTEGER NOT NULL REFERENCES account(id),
  journal_id  INTEGER NOT NULL REFERENCES journal(id),
  amount      NUMERIC (12, 2) NOT NULL,

  -- debit and credit are described by positive and negative value, therefore
  -- zero value is not allowed
  CHECK (amount <> 0)
);

So, basically each transaction will be recorded in journal table and it's amount that is affecting account table will be recorded in posting table.

Since the structure of account is a hierarchical tree, the amount of each node must be calculated with all of it's descendant.

This is the best I can come up with:

WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree(id, parent_id, name, path) AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path -- path from root
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  ),
  -- make a flat table. each account will appear as whenever it's child is
  -- appeared
  flatten AS (
    SELECT unnest(tree.path) AS id,
           tree.id           AS account_id
      FROM tree
     ORDER BY unnest(tree.path), tree.id
  )

SELECT tree.id                               AS id,
       tree.name                             AS name,
       coalesce(abs(sum(posting.amount)), 0) AS amount
  FROM tree
  LEFT OUTER JOIN flatten ON tree.id = flatten.id
  LEFT OUTER JOIN posting ON posting.account_id = flatten.account_id
 GROUP BY tree.id, tree.name
 ORDER BY tree.id

My question is, Can this be improved? Will it perform well?

PS: Some data for testing code

INSERT INTO account VALUES (100, 'ASSET', NULL);
INSERT INTO account VALUES (101, 'Cash', 100);
INSERT INTO account VALUES (102, 'Bank', 100);
INSERT INTO account VALUES (200, 'LIABILITY', NULL);
INSERT INTO account VALUES (201, 'Loan', 200);
INSERT INTO account VALUES (300, 'EQUITY', NULL);
INSERT INTO account VALUES (301, 'Opening Balance', 300);

INSERT INTO journal VALUES (1, 'opening balance', '2018-12-05');
INSERT INTO posting VALUES (1, 101, 1, 1000000.00);
INSERT INTO posting VALUES (2, 301, 1, -1000000.00);

INSERT INTO journal VALUES (3, 'save to bank', '2018-12-05');
INSERT INTO posting VALUES (5, 102, 3, 1250000.00);
INSERT INTO posting VALUES (6, 101, 3, -1250000.00);

INSERT INTO journal VALUES (2, 'loan money', '2018-12-05');
INSERT INTO posting VALUES (3, 101, 2, 500000.00);
INSERT INTO posting VALUES (4, 201, 2, -500000.00);

The output (for running the query above) should be something like this:

 id  |      name       |   amount   
-----+-----------------+------------
 100 | ASSET           | 1500000.00
 101 | Cash            |  250000.00
 102 | Bank            | 1250000.00
 200 | LIABILITY       |  500000.00
 201 | Loan            |  500000.00
 300 | EQUITY          | 1000000.00
 301 | Opening Balance | 1000000.00
(7 rows)

Best Answer

If your account table doesn't change often, you could take advantage of Postgres Materialized Views.

CREATE MATERIALIZED VIEW account_tree 
AS
WITH RECURSIVE
  -- recursively build tree table, assigning path for each row
  tree 
AS (
    SELECT account.id        AS id,
           account.parent_id AS parent_id,
           account.name      AS name,
           ARRAY[account.id] AS path
      FROM account
     WHERE account.parent_id IS NULL

     UNION ALL

    SELECT account.id                     AS id,
           account.parent_id              AS parent_id,
           account.name                   AS name,
           tree.path || ARRAY[account.id] AS path
      FROM account, tree
     WHERE account.parent_id = tree.id
  )
SELECT 
    t.id,
    t.account_id,
    CASE WHEN t.parent_id = t.account_id 
         THEN a.name ELSE t.name END AS name
FROM
    (SELECT id, parent_id, name, unnest(path) as account_id
     FROM   tree) t
JOIN
    account a
    ON a.id = t.parent_id;

Materialized views allows the use of index:

CREATE INDEX account_tree_ix
  ON account_tree (id, account_id);

This view returns next result:

 id | account_id | name           
--: | ---------: | :--------------
101 |        100 | ASSET          
101 |        101 | Cash           
102 |        100 | ASSET          
102 |        102 | Bank           
201 |        200 | LIABILITY      
201 |        201 | Loan           
301 |        300 | EQUITY         
301 |        301 | Opening Balance

Now you can use this view to obtain the balance sheet avoiding the recursive CTE:

SELECT    at.account_id, 
          at.name,
          coalesce(abs(sum(p.amount)), 0) AS amount
FROM      account_tree at
LEFT JOIN posting p
          ON p.account_id = at.id
GROUP BY  at.account_id,
          at.name
ORDER BY  at.account_id;

account_id | name            |     amount
---------: | :-------------- | ---------:
       100 | ASSET           | 1500000.00
       101 | Cash            |  250000.00
       102 | Bank            | 1250000.00
       200 | LIABILITY       |  500000.00
       201 | Loan            |  500000.00
       300 | EQUITY          | 1000000.00
       301 | Opening Balance | 1000000.00

db<>fiddle here

Related Question