Postgres Pro
Downloads
Home   >   mailing lists

Thread: numeric field overflow

numeric field overflow

From
Nurzhan Kirbassov
Date:
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)
)

INSERT INTO test.test VALUES (10000)

Generates an error:

ERROR: numeric field overflow
SQL state: 22003
Detail: A field with precision 5, scale 1 must round to an absolute
value less than 10^4.



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

Thanks.

--
Regards,
Nurzhan Kirbassov.

Re: numeric field overflow

From
Christophe Pettus
Date:
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

Re: numeric field overflow

From
Scott Marlowe
Date:
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.

Re: numeric field overflow

From
Scott Ribe
Date:
> 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

Re: numeric field overflow

From
David W Noon
Date:
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)
=======================================================================

Re: numeric field overflow

From
Bruce Momjian
Date:
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. +