Subject | Re: [firebird-support] Re: NUMERIC or DECIMAL? |
---|---|

Author | Helen Borrie |

Post date | 2004-10-22T23:30:03Z |

At 08:50 PM 22/10/2004 +0000, you wrote:

are very subtle differences when the precision is less than 5.

The precision specifies how many significant digits the number has,

regardless of the scale (the number of digits following the decimal

point). So you would expect this statement to cause an arithmetic

overflow. However, with DECIMAL, it doesn't. If you defined it as

NUMERIC, you would get an overflow with this number.

However, it's not so simple. In fact, NUMERIC(3,2) will accept 123.45 -

which is still not a NUMERIC(3,2) by the rules. Unlike DECIMAL(3,2), it

won't accept 1234.56. (Fb/IB do not conform to standards re fixed numerics!!)

You might be interested to read Geoff Worboys' Tech Info sheet on the

subject of numeric types, at www.ibobjects.com/TechInfo.html

I think Myles was confusing the SQL NUMERIC type with the Paradox Numeric,

which is a floating-point type. (Paradox doesn't have any fixed-precision

types: you have to roll your own by dividing integers).

Re whether "precision should be scale and vice versa" - precision

determines how accurately a number can be represented - if you like, how

many of the significant digits in a number can be relied on. With fixed

precision numbers, a.k.a. "exact precision", every digit can be counted on

to be exact. With floating point numbers, precision "fades" from left to

right.

Hence, with floats, the more capacity the stored number has, the "higher"

its precision. The closer the decimal point is to the left, the closer the

stored number is to "exact". A floating point number is never "exact",

since it's unknown what the scale will be - hence the "floating" decimal point.

A FLOAT type (stored as a 32-bit integer) has reliable precision up to

about six or seven sig. digits. So 0.1234567 is more "exact" than 123456.7,

but they are likely to behave like 0.123457 and 123457.0, respectively. It

might be no less "accurate" to simply truncate the leftmost digits.

By contrast, a NUMERIC(7,7) stores the first number exactly and a

NUMERIC(7,1) stores the second number exactly. The second argument - the

scale (s) - says "this 32-bit integer has to be divided by 10^s when used

in a calculation".

The DOUBLE PRECISION type is a f.p. type that has a "capacity" of up to 18

or 19 sig. digits, i.e. the absolute precision of a 64-bit integer. The

first 15 can be considered reliable.

Ann's rule of thumb is useful: use floating point types for things you

measure, use fixed precision types for things you count (like money!!)

Before you mathematicians decide to start a flame war about floating point

arithmetic: yep, I'm well aware that this does not represent even close to

the whole story about floating-point precision and I'm not going to argue

about the anomalies that occur at the extremities. But I hope it's close

enough to suggest that, when you're dealing with very big and very small

numbers, consult the accountants or the scientists or the statisticians

about the numbers they want you to store and the games they want you to

play with precision and rounding on floating point types.

./hb

> > Quote:Theoretical. In practical terms, NUMERIC and DECIMAL are identical. There

> > The theoretical difference between NUMERIC and DECIMAL is that

>NUMERIC will

> > store exactly p digits, whereas DECIMAL will store not fewer than

>p digits.

are very subtle differences when the precision is less than 5.

>So my question is...where would you use NUMERIC rather than DECIMAL?Except at very low precision, no difference.

>If you used DECIMAL(3,2) and tried to store 1234567.89, would it notNow, does one answer that question with Yes or No? :-)

>allow it?

The precision specifies how many significant digits the number has,

regardless of the scale (the number of digits following the decimal

point). So you would expect this statement to cause an arithmetic

overflow. However, with DECIMAL, it doesn't. If you defined it as

NUMERIC, you would get an overflow with this number.

However, it's not so simple. In fact, NUMERIC(3,2) will accept 123.45 -

which is still not a NUMERIC(3,2) by the rules. Unlike DECIMAL(3,2), it

won't accept 1234.56. (Fb/IB do not conform to standards re fixed numerics!!)

You might be interested to read Geoff Worboys' Tech Info sheet on the

subject of numeric types, at www.ibobjects.com/TechInfo.html

I think Myles was confusing the SQL NUMERIC type with the Paradox Numeric,

which is a floating-point type. (Paradox doesn't have any fixed-precision

types: you have to roll your own by dividing integers).

Re whether "precision should be scale and vice versa" - precision

determines how accurately a number can be represented - if you like, how

many of the significant digits in a number can be relied on. With fixed

precision numbers, a.k.a. "exact precision", every digit can be counted on

to be exact. With floating point numbers, precision "fades" from left to

right.

Hence, with floats, the more capacity the stored number has, the "higher"

its precision. The closer the decimal point is to the left, the closer the

stored number is to "exact". A floating point number is never "exact",

since it's unknown what the scale will be - hence the "floating" decimal point.

A FLOAT type (stored as a 32-bit integer) has reliable precision up to

about six or seven sig. digits. So 0.1234567 is more "exact" than 123456.7,

but they are likely to behave like 0.123457 and 123457.0, respectively. It

might be no less "accurate" to simply truncate the leftmost digits.

By contrast, a NUMERIC(7,7) stores the first number exactly and a

NUMERIC(7,1) stores the second number exactly. The second argument - the

scale (s) - says "this 32-bit integer has to be divided by 10^s when used

in a calculation".

The DOUBLE PRECISION type is a f.p. type that has a "capacity" of up to 18

or 19 sig. digits, i.e. the absolute precision of a 64-bit integer. The

first 15 can be considered reliable.

Ann's rule of thumb is useful: use floating point types for things you

measure, use fixed precision types for things you count (like money!!)

Before you mathematicians decide to start a flame war about floating point

arithmetic: yep, I'm well aware that this does not represent even close to

the whole story about floating-point precision and I'm not going to argue

about the anomalies that occur at the extremities. But I hope it's close

enough to suggest that, when you're dealing with very big and very small

numbers, consult the accountants or the scientists or the statisticians

about the numbers they want you to store and the games they want you to

play with precision and rounding on floating point types.

./hb