numeric

From: Patrick Bakker <patrick(at)vanbelle(dot)com>
To: "PostgreSQL General (E-mail)" <pgsql-general(at)postgresql(dot)org>
Subject: numeric
Date: 2002-08-28 19:13:06
Message-ID: A9CE1D556F89DD4FBA4CF797215DF61A02F35A@20svbl1.vanbelle.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I was just trying pgadmin II and I noticed that my tables using numeric
values are displayed as numeric(65535,65531). I'm wondering if this is
possible (or is pgadmin II displaying the default values wrong?) - it looks
like a crazy value. Also, is there a way to display the numeric precision
and scale information in psql?

Since I was using the default mapping for JBoss 3.0.0/PostgreSQL 7.2, I
checked the standardjbosscmp-jdbc.xml file in the conf directory of the
JBoss server directory and changed the java.lang.BigDecimal mapping from
'numeric' to 'numeric(18,5)'. Dropping the table and restarting JBoss has
corrected the problem to a sane value.

However, this little scenario prompted me to reconsider how I was using
numeric. I'm wondering what numeric precision & scale people are using for
money and quantities.
Currently, my application needs to work with Canadian and American dollars
and Dutch guilders but I'd like to make it fully international. My
quantities are mostly integer quantities (ie. the physical count of items in
inventory) but the way I have inventory items designed allows for different
units of measure to be used with each item. This means some inventory items
could have a fractional quantity (ie. if they are quantities of weight,
area, length or time).

My current thought is:
MONEY
Each tuple with money has a 'fk_currency' field which indicates which
currency the money will be in (for that entire tuple).
numeric (18, 5)
This allows almost 10 billion dollars to be stored in a single field which
is way beyond anything I need now but seems unlikely to be exceeded
in any tuple with a money field in it. However, I wonder if it is good
practise to store everything in terms of dollars or if it is better to use
the most
basic unit of currency (ie. cents).

QUANTITY
'fk_uom' (There may be several fk_uom links in the same tuple (ie.
fk_quantity_uom, fk_weight_uom, fk_distance_uom)
numeric (18, 5)
No reason at all except that its a big number. How much precision to
people generally want for quantity fields?

Any thoughts?
Patrick

Browse pgsql-general by date

  From Date Subject
Next Message Markus Wollny 2002-08-28 21:25:44 Re: Naming-scheme for db-files
Previous Message Andrew Sullivan 2002-08-28 19:09:20 Re: Noobie Questions...