On Fri, Jul 22, 2011 at 10:42 AM, Rich Shepard <[email protected]> wrote: > On Fri, 22 Jul 2011, WR Consulting wrote: > >> Rich, one of the things that I found quite surprising about SL when I >> first started "peeking under the hood" was its use of a floating-point >> type for monetary amounts. > > ARRRGGH-H-H-H!!! > > I learned not to do this in 1984 with dBASE II. Even with today's dbms > (such as postgres) you cannot select rows based on a floating point number. > This is a terribly eggregious design/coding error. > > If this is still the case, changing all those floats to (for example) > numeric(8,2) will cover all of us with bank balances < 99,999,999.99 in our > local currencies.
First, there are other cases where fractional cents may be tracked (for example sales tax liabilities) but they shouldn't affect bank deposits or withdrawals. What I'd recommend doing first is just transforming the field to an unbounded numeric type I.e. without parentheses. This represents the money in PostgreSQL's arbitrary precision math form (internally a string of 10-bit units, each representing an integer from one to 1000. Why 1000? Because it's more efficient to lose 24 bits per 1024 to overhead than 6 per 16). You can do this by (pg 8.1 and above): ALTER TABLE acc_trans ALTER COLUMN amount TYPE numeric; or (8.0 and below) ALTER TABLE acc_trans ADD COLUMN amount2 NUMERIC; UPDATE acc_trans set amount2 = amount; ALTER TABLE acc_trans DROP COLUMN amount; ALTER TABLE acc_trans RENAME column amount2 TO amount; That may not fix your problem though. The first question what does SQL-Ledger think your balance should be? Are there transactions of exactly that amount which were erroneously committed in the past? If you have old bank statements, you can run successive queries to try to determine where and when this problem started. Best Wishes, Chris Travers _______________________________________________ SQL-Ledger mailing list [email protected] http://lists.ledger123.com/mailman/listinfo/sql-ledger
