Subject RE: [firebird-support] Re: Decimal field precision question Rick Debay 2007-11-19T21:59:29Z
> I think that I'm going to standardize on using NUMERIC instead of
DECIMAL so that if I ever need to store smaller numerics (pp.ss), I will
save myself 2 bytes per field

Do what's correct, not based on what a side-effect of the implementation

If the data is of a certain precision and scale, but is allowed to be
more precise, then use DECIMAL. If it is NOT allowed to be more
precise, then use NUMERIC.
Hopefully later versions of Firebird will use something similar to
Java's BigDecimal as the underlying representation so it will restrict
the data correctly, and allow the upper size limits to be unbounded.

-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] On Behalf Of jeff_j_dunlap
Sent: Thursday, September 27, 2007 4:24 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Decimal field precision question

> I think the type you wish is Decimal(6,3) theorically 999.999.
Decimal
> (3,3) would be something like 0.999.
>
> *But* FB does not impose the limit by itself, you need to use a CHECK
> CONSTRAINT to limit the maximum filed value, the type used by FB to
> store decimal(6,3) is a 32 bit integer and the maximum allowed values
> will be 2^31/1000

Thanks for the response. I read the IB6 docs a little more and did a
litte more testing.

In my limited experience, it appears that NUMERICS (with scale) and
DECIMALS (with scale) store the date exactly the same way, except when
dealing with smaller figures such as NUMERIC(pp.ss) and below. The
reason is that according to the IB6 docs, a NUMERIC(pp.ss) is stored as
a smallint and a DECIMAL(pp.ss) is stored as an integer. This basically
means that if you only need to store figures in the pp.ss range, using a
NUMERIC(pp.ss) vs a DECIMAL(pp.ss) will save you 2 bytes for each field
since an integer occupies 4 bytes and a smallint occupies 2 bytes.

I'm going to stick with Decimal(6,3) for storing interest rates ranging
from p.sss to ppp.sss

I'm learning FB using dialect 3 and as far as that goes, I can't see a
difference between choosing NUMERIC(precision.scale) or DECIMAL
(precision.scale). With this said, I think that I'm going to
standardize on using NUMERIC instead of DECIMAL so that if I ever need
to store smaller numerics (pp.ss), I will save myself 2 bytes per field.

Best Regards,

Jeff

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item on the