Validation numeric (12,2) Postgres

3

I have the following question, I have a field in the Postgresql database of type numeric (12,2) when sending a value of a form I did not find a way to validate the value in PHP to insert this value in the database. >

Error generated: Numeric value out of range: 7 ERROR: numeric field overflow DETAIL: A field with precision 12, scale 2 should round to an absolute value less than 10 ^ 10.

    
asked by anonymous 15.10.2015 / 21:17

2 answers

3

If you want to know if the value entered is greater than the column limit, take the error hint, make 10 to 10 ( 10^10 ), play on a variable or constant and make the comparisons.

<?php
   define('LIMITE', 9999999999.99);
   $valor = 90000000000;
   if($valor > LIMITE ){
      echo 'vai estoura o limite';
  }
    
15.10.2015 / 22:17
2

A very common error when working with type numeric(precisão, escala) is to find that precision indicates how many digits will fit in the whole part.

In fact, accuracy will indicate how many digits the integer will have, including the decimal places.

See the following example:

SELECT CAST(123456 AS NUMERIC(6, 4))

It will pop the field as it will be interpreted as 123456.0000

So, for the example type, the highest value that will fit in the field is 99.9999

Even the value 99.99999 will not be accepted as it will end up being rounded to 100.0000 Already the value 99.99994 will be accepted as it will be rounded to 99.9999

To know the highest value accepted by a NUMERIC field, do ((10 ^ precisão)/(10 ^ escala)) - (1/(10^escala))

Example using NUMERIC(3, 3)

((10 ^ 3)/(10 ^ 3)) - (1/(10 ^ 3))
(1000/1000) - (1/1000)
1 - 0.001
0.999

This is one of the situations I learned from day to day, because not even the manual informs the formula of knowing what the greatest value of a field.

  

Note: You can not have field precision less than the

    
16.10.2015 / 02:17