Quantcast

numeric field overflow

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

numeric field overflow

Nurzhan Kirbassov
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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: numeric field overflow

Christophe Pettus-2

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
    [hidden email]


--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: numeric field overflow

Scott Marlowe-2
In reply to this post by Nurzhan Kirbassov
On Tue, Oct 6, 2009 at 12:20 AM, Nurzhan Kirbassov <[hidden email]> 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.

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: numeric field overflow

Scott Ribe
In reply to this post by Nurzhan Kirbassov
> 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
[hidden email]
http://www.killerbytes.com/
(303) 722-0567 voice



--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: numeric field overflow

David W Noon-2
In reply to this post by Nurzhan Kirbassov
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]
=======================================================================
[hidden email] (David W Noon)
=======================================================================

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: numeric field overflow

Bruce Momjian
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  <[hidden email]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

--
Sent via pgsql-general mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Loading...