Thread: Different exponent in error messages
I have NUMERIC(9,3) field. Postgres produces different (!) error messages when inserted value is too big to fit into this field ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.; DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.; DETAIL: The absolute value is greater than or equal to 10^7 for field with precision 9, scale 3.; Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for field with precision 9, scale 3 ? Andrus.
"Andrus" <eetasoft@online.ee> writes:
> Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for
> field with precision 9, scale 3 ?
Could we see a complete test case --- ie, what are the input values
causing these messages?
regards, tom lane
On Wed, Jan 11, 2006 at 04:09:23PM -0500, Tom Lane wrote: > "Andrus" <eetasoft@online.ee> writes: > > Why Postgres 8.1.1 in XP returns different exponents ( 10^6, 10^9, 10^7) for > > field with precision 9, scale 3 ? > > Could we see a complete test case --- ie, what are the input values > causing these messages? I think this is what Andrus is seeing: test=> CREATE TABLE foo (n numeric(9,3)); CREATE TABLE test=> INSERT INTO foo VALUES (1000000); ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3. test=> INSERT INTO foo VALUES (1000000000); ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3. test=> INSERT INTO foo VALUES (10000000); ERROR: numeric field overflow DETAIL: The absolute value is greater than or equal to 10^7 for field with precision 9, scale 3. -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> I think this is what Andrus is seeing:
> test=> CREATE TABLE foo (n numeric(9,3));
> CREATE TABLE
> test=> INSERT INTO foo VALUES (1000000);
> ERROR: numeric field overflow
> DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3.
> test=> INSERT INTO foo VALUES (1000000000);
> ERROR: numeric field overflow
> DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3.
Hm, I thought I tested that same case, but I must've messed up somehow.
Anyway, the code seems to be intentionally reporting the log10 of the
actual input value, not the limiting log10 for the field size. This
behavior goes at least as far back as PG 7.0, so I'm disinclined to
change it. We could talk about altering the message wording though,
if you have a suggestion for something you'd find less confusing.
Pre-7.4 versions say
ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3
so it looks like we just fixed the grammar during the 7.4 message
wording cleanup, without reflecting about whether the meaning was clear.
regards, tom lane
On Wed, 2006-01-11 at 16:52, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > I think this is what Andrus is seeing: > > > test=> CREATE TABLE foo (n numeric(9,3)); > > CREATE TABLE > > test=> INSERT INTO foo VALUES (1000000); > > ERROR: numeric field overflow > > DETAIL: The absolute value is greater than or equal to 10^6 for field with precision 9, scale 3. > > test=> INSERT INTO foo VALUES (1000000000); > > ERROR: numeric field overflow > > DETAIL: The absolute value is greater than or equal to 10^9 for field with precision 9, scale 3. > > Hm, I thought I tested that same case, but I must've messed up somehow. > > Anyway, the code seems to be intentionally reporting the log10 of the > actual input value, not the limiting log10 for the field size. This > behavior goes at least as far back as PG 7.0, so I'm disinclined to > change it. We could talk about altering the message wording though, > if you have a suggestion for something you'd find less confusing. > Pre-7.4 versions say > > ERROR: overflow on numeric ABS(value) >= 10^9 for field with precision 9 scale 3 > > so it looks like we just fixed the grammar during the 7.4 message > wording cleanup, without reflecting about whether the meaning was clear. Does the SQL spec say anything about the error message? I can't remember, as it's not a part of the spec I'm real familiar with.