Обсуждение: Numeric field quirk [Again]
Greetings, Well, it seems that the numeric issue I was having has nothing to do with the precision and scale being set the same, it has to do with the input data. The precision has to be at least 2 greater than the biggest number you need to enter, i.e. equinox=# create table test ( d numeric(4,2) ); CREATE equinox=# insert into test values(10); INSERT 167844 1 equinox=# insert into test values(101); ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 scale 2 Matthew
Matthew Hagerty <matthew@venux.net> writes: > Well, it seems that the numeric issue I was having has nothing to do with > the precision and scale being set the same, it has to do with the input > data. The precision has to be at least 2 greater than the biggest number > you need to enter, i.e. > equinox=# create table test ( d numeric(4,2) ); > CREATE > equinox=# insert into test values(10); > INSERT 167844 1 > equinox=# insert into test values(101); > ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 > scale 2 Precision is the total number of digits, scale is the number of digits to the right of the decimal point. So the above (4,2) means numbers like XX.XX --- and obviously 10 fits, 101 doesn't. Your previous example of (3,3) means numbers like .XXX --- no integer will fit, but fractions will. I see no bug here... regards, tom lane
Matthew Hagerty wrote: > Greetings, > > Well, it seems that the numeric issue I was having has nothing to do with > the precision and scale being set the same, it has to do with the input > data. The precision has to be at least 2 greater than the biggest number > you need to enter, i.e. > > equinox=# create table test ( d numeric(4,2) ); > CREATE > equinox=# insert into test values(10); > INSERT 167844 1 > equinox=# insert into test values(101); > ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 > scale 2 The scale digits aren't added, they are taken from the precision specified digits. Numeric(4.2) is 99.99, numeric(8.2) is 999999.99 and numeric(4.4) is .9999 - so the above looks correct to me. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #
> Greetings, > > Well, it seems that the numeric issue I was having has nothing to do with > the precision and scale being set the same, it has to do with the input > data. The precision has to be at least 2 greater than the biggest number > you need to enter, i.e. > > equinox=# create table test ( d numeric(4,2) ); > CREATE > equinox=# insert into test values(10); > INSERT 167844 1 > equinox=# insert into test values(101); > ERROR: overflow on numeric ABS(value) >= 10^2 for field with precision 4 > scale 2 The variable above is 4 digits, with 2 digits to the right of the decimal place. Doing numberic(3,3) allows insertion of 0.4, but not 4.0. This is expected. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026