Validation Numeric(12,2) Postgres

Asked

Viewed 1,047 times

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 could not find a way to validate the value in PHP to insert this value in the database.

Generated error: Numeric value out of range: 7 ERROR: field bursting Numeric DETAIL: A field with accuracy 12, scale 2 must round to an absolute value less than 10.

php postgresql

  • They are values that come to integration. They are Nfs values, wanted something to validate these values not to burst in the bank.

  • can you change the field type? when you say NF values is talking about right money?

  • 1

    I can not change the type we have many registered values and other points to register in this table. Yes money.

  • 1

    That’ll be of some help? <?php&#xA;define('LIMITE', 10000000000);&#xA;$valor = 90000000000;&#xA;if($valor > LIMITE ){&#xA; echo 'vai estoura o limite';&#xA;}

  • It worked! Thank you so much @rray !

2 answers

3


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

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

  • @Marcosregis points out the error there, I made the wrong account? I can correct or delete if there is no salvation.

  • @Marcosregis thank you so much for the correction! D

3

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

In fact, precision will indicate how many digits the whole number will have, including the decimal places.

See the following example:

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

The field will burst because 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 99.99999 will not be accepted, as it will eventually be rounded to 100.0000 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 on a day-to-day basis, because not even in the manual does the formula of knowing what is the highest value of a field.

Note: It is not possible to have field with less precision than scale

Browser other questions tagged php postgresql

You are not signed in. Login or sign up in order to post.