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 |
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 |
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 |
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 |
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 |
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 |
Powered by Nabble | Edit this page |