numeric field overflow

On Oct 5, 2009, at 11:20 PM, Nurzhan Kirbassov wrote:

So, does the precision part of the numeric type really means number of
digits to the left of the decimal point, or what ?

NUMERIC is behaving as documented. The way to think of it is when you
are inserting:

INSERT INTO test.test VALUES (10000)

into a NUMERIC(5,1), what you are doing is inserting:

INSERT INTO test.test VALUES (10000.0)

10000.0 has six significant digits, rather than five, so the insert
fails.
--
-- Christophe Pettus
xof@thebuild.com

On Tue, Oct 6, 2009 at 12:20 AM, Nurzhan Kirbassov <km.sameboy@gmail.com> wrote:

Good day.

I may be misunderstanding the NUMERIC type description in the manual,
so can anyone please help me with this? Description says:

"The scale of a numeric is the count of decimal digits in the
fractional part, to the right of the decimal point. The precision of a
numeric is the total count of significant digits in the whole number,
that is, the number of digits to both sides of the decimal point. ...
Integers can be considered to have a scale of zero. "

However, I am not able to insert numbers that have number of digits
equal to the precision and the scale equal to 0.

F.E.:

CREATE TABLE test.test
(
 rate numeric(5,1)
)

This declares a numeric of 5 digits, with 1 to the right of the decimal point.

INSERT INTO test.test VALUES (10000)

Generates an error:

Like it should. however this:

CREATE TABLE test.test ( rate numeric(5,0));
INSERT INTO test.test VALUES (10000);
INSERT 0 1

Works just fine.

However, I am not able to insert numbers that have number of digits
equal to the precision and the scale equal to 0.

Scale applies to the *column*. You defined your column to have a scale of 1,
so you can't just claim that your value has scale 0 and claim an extra digit
to the left of the decimal point.

--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On Tue, 6 Oct 2009 12:20:11 +0600, Nurzhan Kirbassov wrote about
[GENERAL] numeric field overflow:

[snip]

So, does the precision part of the numeric type really means number of
digits to the left of the decimal point, or what ?

No.

The precision is the *total* number of decimal digits, both to the left
and to the right of the decimal point.
--
Regards,

Dave [RLU #314465]
=======================================================================
david.w.noon@ntlworld.com (David W Noon)
=======================================================================

David W Noon wrote:

On Tue, 6 Oct 2009 12:20:11 +0600, Nurzhan Kirbassov wrote about
[GENERAL] numeric field overflow:

[snip]

So, does the precision part of the numeric type really means number of
digits to the left of the decimal point, or what ?

No.

The precision is the *total* number of decimal digits, both to the left
and to the right of the decimal point.

Yes, this is confusing, but it is how the standard defines the behavior.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +