Postgresql – Meaning of precision 1, scale 0 in error message

errorsnumber formattingpostgresql

What datatype does this error message refer to?

ERROR:  numeric field overflow
DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

I'm getting the error listed above, it's coming from an insert trigger. Since none of my datatype are explicitly defined as NUMERIC(1,0) and since the maximum value is 10 to the power of 1 (i.e. 10) I'm guessing that this error is actually referring to a BOOLEAN field. However, I can't find confirmation of this.

UPDATE
Solved. My trigger is attempting to parse some strings into numbers. The error listed is returned when to_number() doesn't get a string in the expected format.

This returns 1.4 (correct) –

select to_number('1.4','9D9');

This returns the aforementioned error

select to_number('1d4','9D9');

Still not quite sure why it's complaining about NUMERIC(1,0) but at least I know why there's an error.

Best Answer

That's because to_number() drops anything but data characters from the input string. Data characters are: digits, sign, decimal point, and comma. Noise characters are not insignificant though, as they still count against the length of the format pattern. So:

SELECT to_number('1d4','9D9');

is effectively the same as:

SELECT to_number('14','9D9');

Both fail because the pattern '9D9' only allows a single digit before the comma.

But this valid expression:

SELECT to_number('1X4','99D9');  -- added digit to pattern

is not the same as:

SELECT to_number('1XXX4','99D9');

To much noise truncates the input.

db<>fiddle here

And, yes, D is a format specifier for a decimal point (independent of locale) in the pattern string (2nd function argument), but not in the input string (1st function argument). You found that yourself already.

To address the question in the title - Postgres has a definition in the manual:

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. The scale of a numeric is the count of decimal digits in the fractional part, to the right of the decimal point. So the number 23.5141 has a precision of 6 and a scale of 4. Integers can be considered to have a scale of zero.

That said, I see two Postgres issues here:

1. Missing documentation

It's not documented in the manual that to_number() ignores non-data characters in the input. Nor that those still count against the total length of the pattern.

2. Bug in error message

There seems to be an error in the DETAIL of the error message. The observed:

DETAIL:  A field with precision 1, scale 0 must round to an absolute value less than 10^1.

Should really be:

DETAIL:  A field with precision 2, scale 1 must round to an absolute value less than 10^1.

The effect is the same, so the behavior is coherent. But the information is misleading.

Related Question