Numeric (M,n) can insert a range of values

Recently read the time to see such a sentence:

for numeric values that have a precision declared, an error is made if the value of insert insertion is greater than the declared precision range.

So what is the range of precision here? The numeric value represented by the precision is the number of digits. That 3 means the maximum value is 999.

Tested a bunch of data found as follows:

If the precision and scale are not declared, the inserted values remain the same, with no scope constraints. (certainly not exceeding the precision and scale that can be achieved within the system).

testdb=# CREATE TABLE numeric (N1numeric (3,0), N2 numeric (3,0), N3 numeric (3,2), N4 Numeric);

CREATE TABLE

testdb=# INSERT INTO numericvalues (3.1,3.5,3.12,3.123);

INSERT 0 1

testdb=# select * from numeric;

N1 |N2 |  N3 | N4

----+----+------+-------

3| 4 | 3.12 | 3.123

(1 row)

As you can see, the values that exceed the scale are actually inserted in a rounded manner.

testdb=# INSERT into numeric values (999.4999,5,9.991,13.123);

INSERT 0 1

testdb=# INSERT INTO numericvalues (999.5,5,9.994,13.123);

error:numeric Field Overflow

detail:a field with precision 3, scale 0 must round to a absolute value lessthan.

Statement:insert into numeric values (999.5,5,9.994,13.123);

error:numeric Field Overflow

detail:a field with precision 3, scale 0 must round to a absolute value lessthan.

These two sets of data mean that the maximum value that numeric (3,0) can insert is 999.4999 (9 loops), which is always less than 999.5.

This time I think that numeric (3,2) is not meant to be less than 999.49. Continue inserting the value and found the critical value:

testdb=# INSERT INTO numericvalues (999.4999,5,9.994999999999999999,13.123);

INSERT 0 1

testdb=# INSERT INTO numericvalues (1,2,9.995,22.123);

error:numeric Field Overflow

detail:a field with precision 3, scale 2 must round to a absolute value lessthan.

Statement:insert into numeric values (1,2,9.995,22.123);

error:numeric Field Overflow

detail:a field with precision 3, scale 2 must round to a absolute value lessthan.

These two sets of data show that the maximum value of numeric (3,2) is 9.99499999 (9 loops), which is always less than 9.995. This means that the so-called precision range is to be combined with the scale of a range, the accuracy of the number of digits is to be the scale (after the decimal point) counted.

Without the precision and scale of the numerical range (the system can withstand the range of precision) I did not test out, this is not very meaningful.

testdb=# INSERT INTO numeric

Values (999.4999999,999.49999999999,9.99499999999999999,99999999999999.99999999);

INSERT 0 1

testdb=# select * from numeric;

N1 |N2 |           N3 | N4

-----+-----+------+-------------------------

3| 4 |                   3.12 | 3.123

5| 4 |                   3.12 | 3.123

5| 4 |                   5.12 | 3.123

13| 4 |                   5.12 | 3.123

13| 4 |                  8.12 | 13.123

999| 4 |                  9.12 | 13.123

999| 5 |                  9.99 | 13.123

999| 5 |                  9.99 | 13.123

999| 5 |                  9.99 | 13.123

999| 5 |                  9.99 | 13.123

999| 999 | 9.99 | 99999999999999.99999999

(rows)