International SQL-Ledger Network Association

1. Create a temporary table using psql so that we do not get id in the chart file.

  CREATE TABLE chart_tmp AS SELECT accno, description, charttype, category, link, gifi_accno, contra, allow_gl FROM chart;

2. Now dump the table in SQL format using following command:

  pg_dump -U postgres --table=chart_tmp --column-inserts ledger28 > chart.sql

3. Delete all command before first INSERT. These are not needed.

4. Edit the generated file and search / replace 'chart_tmp' with 'chart' for correct table name.

5. Add following lines and adjust accordingly according to the chart of accounts.

  insert into tax (chart_id,rate) values ((select id from chart where accno = '2310'),0.1);

  insert into tax (chart_id,rate) values ((select id from chart where accno = '2320'),0.14);

  insert into tax (chart_id,rate) values ((select id from chart where accno = '2330'),0.3);

–

INSERT INTO defaults (fldname, fldvalue) VALUES ('inventory_accno_id', (SELECT id FROM chart WHERE accno = '1520'));

INSERT INTO defaults (fldname, fldvalue) VALUES ('income_accno_id', (SELECT id FROM chart WHERE accno = '4020'));

INSERT INTO defaults (fldname, fldvalue) VALUES ('expense_accno_id', (SELECT id FROM chart WHERE accno = '5010'));

INSERT INTO defaults (fldname, fldvalue) VALUES ('fxgain_accno_id', (SELECT id FROM chart WHERE accno = '4450'));

INSERT INTO defaults (fldname, fldvalue) VALUES ('fxloss_accno_id', (SELECT id FROM chart WHERE accno = '5810'));

INSERT INTO defaults (fldname, fldvalue) VALUES ('weightunit', 'kg');

INSERT INTO defaults (fldname, fldvalue) VALUES ('precision', '2');

–

INSERT INTO curr (rn, curr, prec) VALUES (1,'USD',2);

INSERT INTO curr (rn, curr, prec) VALUES (2,'CAD',2);

INSERT INTO curr (rn, curr, prec) VALUES (3,'EUR',2);

For gifi accounts you just need to run

  pg_dump -U postgres --table=gifi --column-inserts ledger28 > gifi-chart.sql

and then remove any unwanted text before first INSERT.